mysql查询关于时间的查询语句_mysql 时间查询

1.查询今年的数据

where YEAR(create_time) = YEAR(CURDATE()) 

2.查询今年每个月的数据

 
SELECT a.mon,IFNULL(b.count,0) AS count
FROM ( 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 11 MONTH),'%m') AS mon 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 10 MONTH),'%m') AS mon 
    UNION ALL 
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 9 MONTH),'%m') AS mon 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 8 MONTH),'%m') AS mon 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 7 MONTH),'%m') AS mon 
    UNION ALL 
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 6 MONTH),'%m') AS mon 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 5 MONTH),'%m') AS mon 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 4 MONTH),'%m') AS mon 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH),'%m') AS mon 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH),'%m') AS mon 
    UNION ALL 
    SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%m') AS mon 
    UNION ALL 
    SELECT DATE_FORMAT(CURDATE(),'%m') AS mon 
    ) a  LEFT JOIN ( 
    SELECT DATE_FORMAT(create_time,'%m')  AS DATETIME, count(1) AS count
    FROM t_operation_eye_of_heaven
    WHERE YEAR(create_time) = YEAR(CURDATE()) AND `status`>0
    GROUP BY DATE_FORMAT(create_time,'%Y-%m') 
) b ON a.mon = b.datetime

3.查询当天的数据

WHERE create_time between #{create_time} and now() 

4. 查询每个月不同状态的数据

SELECT
            DATE_FORMAT(happen_time,'%Y-%m') date,
            SUM(CASE WHEN t.`status` >0  THEN 1 ELSE 0 END) submitCount,
            SUM(CASE WHEN t.`flag` = 1 THEN 1 ELSE 0 END) releaseCount,
            SUM(CASE WHEN t.`flag` = 2 THEN 1 ELSE 0 END) noPassCount,
            SUM(CASE WHEN t.`flag` = 3 THEN 1 ELSE 0 END) yjCount,
            SUM(CASE WHEN t.`amount` is not null THEN amount ELSE 0 END) totalMoney
        FROM
            t_operation_eye_of_heaven t
            <where>
                <if test="date !='' and date !=null">
                    and  happen_time BETWEEN #{startTime} and #{endTime}
                </if>
            </where>
            GROUP BY DATE_FORMAT(happen_time,'%Y-%m')
            order by DATE_FORMAT(happen_time,'%Y-%m') desc