sql时间查询

查询n天之前的

TO_DAYS(NOW()) - TO_DAYS(time) = ?

查询前n天之内的(包括了今天)

DATE_SUB(CURDATE(), INTERVAL ? DAY) < date(time)

查询本月

DATE_FORMAT(time,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m')

查询上个月

DATE_FORMAT(create_time,'%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 MONTH),'%Y-%m')

查询第n个月前的

WHERE PERIOD_DIFF(DATE_FORMAT(NOW(),'%Y%m'), DATE_FORMAT(time,'%Y%m')) = ?

查询n个月之内的

DATE_SUB(CURDATE(), INTERVAL ? MONTH) < time

查询本年

DATE_FORMAT(time,'%Y') = DATE_FORMAT(CURDATE(),'%Y') 
或
YEAR(time)=YEAR(NOW())

昨天数据

date(create_date) = date_sub(curdate(),interval 1 day)

昨天到今天数据

TO_DAYS( NOW( ) ) - TO_DAYS(create_date) <= 1

七天前数据

DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(create_date)

30天前数据

DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(create_date)

查询一周数据没有补0     (curdate( )可改为指定时间，则查询指定时间一周数据)

SELECT
                b.weekday as totalDate,b.total
                FROM
                (
                SELECT
                a.weekday,
                 IFNULL(count(nm.id) ,0) as total
                FROM
                (
                SELECT
                date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 0 DAY ) AS weekday UNION
                SELECT
                date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 1 DAY ) UNION
                SELECT
                date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 2 DAY ) UNION
                SELECT
                date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 3 DAY ) UNION
                SELECT
                date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 4 DAY ) UNION
                SELECT
                date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 5 DAY ) UNION
                SELECT
                date_sub( curdate( ), INTERVAL WEEKDAY( curdate( ) ) - 6 DAY ) 
                ) AS a
                LEFT JOIN tmd_snap AS nm ON date_format( nm.create_date, '%Y-%m-%d' ) = a.weekday 
                GROUP BY
                a.weekday 
                ORDER BY 
                a.weekday 
                ) b,
                ( SELECT @rownum /*'*/:=/*'*/ 0 ) r

#统计本月每天计数没有补0

SELECT
    adddate( '2018-03-01', numlist.id ) AS 'date',
    IFNULL( selfinspection.count, 0 ) AS count 
FROM
    (
    SELECT
        n1.id + n10.id * 10 + n100.id * 100 AS id 
    FROM
        tmd_num n1
        CROSS JOIN tmd_num AS n10
        CROSS JOIN tmd_num AS n100 
    ) AS numlist
    LEFT JOIN (
    SELECT
        DATE_FORMAT( create_date, '%Y-%m-%d' ) AS create_date,
        count(*) AS count 
    FROM
        tmd_safety_selfinspection 
    WHERE
        category = "1" 
    GROUP BY
    DATE_FORMAT( create_date, '%Y-%m-%d' )) AS selfinspection ON selfinspection.create_date = adddate( '2018-03-01', numlist.id ) 
WHERE
    adddate( '2018-03-01', numlist.id ) >= date_add( curdate(), INTERVAL - DAY ( curdate()) + 1 DAY ) 
    AND adddate( '2018-03-01', numlist.id ) <= last_day(
    curdate()) 
GROUP BY
    adddate( '2018-03-01', numlist.id ) 
ORDER BY
    adddate( '2018-03-01', numlist.id )

#统计指定月份消费

SELECT
    adddate( '2018-03-01', numlist.id ) AS 'date',
    IFNULL( consumption.breakfast, 0 ) AS breakfast,
    IFNULL( consumption.lunch, 0 ) AS lunch,
    IFNULL( consumption.dinner, 0 ) AS dinner,
    0 + CAST(IFNULL( sum(recharge.recharge_amount) /10, 0 ) AS CHAR)  AS recharge_amount  
FROM
    (SELECT n1.id + n10.id * 10 + n100.id * 100 AS id FROM tmd_num n1
        CROSS JOIN tmd_num AS n10
        CROSS JOIN tmd_num AS n100 
    ) AS numlist
    LEFT JOIN (SELECT
        DATE_FORMAT( consumption.create_date, '%Y-%m-%d' ) AS create_date,
        SUM( CASE `consumption_type` WHEN '0' THEN consumption_amount ELSE 0 END ) AS 'breakfast',
        SUM( CASE `consumption_type` WHEN '1' THEN consumption_amount ELSE 0 END ) AS 'lunch',
        SUM( CASE `consumption_type` WHEN '2' THEN consumption_amount ELSE 0 END ) AS 'dinner' 
    FROM
        tmd_consumption_info AS consumption where consumption.stu_name = '张三' GROUP BY DATE_FORMAT( consumption.create_date, '%Y-%m-%d' )
        ) AS consumption ON consumption.create_date = adddate( '2018-03-01', numlist.id ) 
LEFT JOIN tmd_recharge as recharge on DATE_FORMAT(recharge.create_date,'%Y-%m-%d') = adddate( '2018-03-01', numlist.id ) AND recharge.stu_name = '张三'
WHERE
    adddate( '2018-03-01', numlist.id ) >= '2022-05-01' #开始时间
    AND adddate( '2018-03-01', numlist.id ) <= '2022-05-31' %结束时间
GROUP BY
    adddate( '2018-03-01', numlist.id ) 
ORDER BY
    adddate('2018-03-01',numlist.id)