mysql:如何获取从去年7月到当前日期的数据

jdgnovmf  于 2021-06-21  发布在  Mysql
关注(0)|答案(4)|浏览(377)

我知道如何获取七月的数据

SELECT * FROM tableName 
   WHERE MONTH(created) = 7

但是我怎样才能得到从去年七月到现在的数据呢?

lmvvr0a8

lmvvr0a81#

根据你的询问,我同意:

SELECT * FROM tableName 
WHERE MONTH(created) = 7 
AND (
( MONTH(CURDATE()) <= 7 AND YEAR(created) = YEAR(CURDATE()) - 1 )
OR 
( MONTH(CURDATE()) > 7 AND YEAR(created) = YEAR(CURDATE()) )
)

如果您想要(如您在评论中所问)自那时起的数据,请使用:

SELECT * FROM tableName 
    WHERE created <= CURDATE()
    AND (
    (
    MONTH(CURDATE()) <= 7
    AND created >= DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-07-01'), INTERVAL 1 YEAR)
    )
    OR
    (
    MONTH(CURDATE()) > 7
    AND created >= DATE_FORMAT(CURDATE(), '%Y-07-01')
    ));
kuuvgm7e

kuuvgm7e2#

用于以下查询。

SELECT 
  * 
FROM
  `tableName`
WHERE MONTH(created) = 7 
AND
(CASE WHEN (MONTH(CURRENT_DATE()) < 7) 
 THEN
       YEAR(created) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))
 ELSE
       YEAR(created) = YEAR(CURRENT_DATE())
 END)
yquaqz18

yquaqz183#

如果你想要去年七月以来的数据,那么你需要找到去年七月的日期。

select t.*
from t
where (month(now()) > 7 and t.created >= date(concat(year(now()), '-01-01')) or
      (month(now()) <= 7 and t.created >= date(concat(year(now()) - 1, '-01-01')) ;
zd287kbt

zd287kbt4#

兄弟,现在试试。。。

SELECT * FROM  Table_Name
WHERE event_date between (case when month(GETDATE()) >6
                                then cast( concat(year( GETDATE()),'-07-01')as datetime)    
                                    else cast( concat(year( GETDATE())-1,'-07-01')   as datetime) 
                                    end)  and GETDATE()

相关问题