SQL按年月日查询

一、年度统计

1、统计本年且按某一类型统计数据

SELECT sdd.base_name AS name1,
       ifnull(count(DISTINCT a.base_id)+count(DISTINCT c.base_id),0) AS count1

       FROM sys_dict_data sdd

       LEFT JOIN table1 a ON a.typeid = sdd.base_id 
       AND DATE_FORMAT(a.time,'%Y') = DATE_FORMAT(NOW(),'%Y')
       LEFT JOIN table2 c ON c.type = sdd.base_id 
       AND DATE_FORMAT(c.time,'%Y') = DATE_FORMAT(NOW(),'%Y')

       WHERE sdd.type_id = '.....' and sdd.is_delete = 0
       GROUP BY sdd.base_name
       ORDER BY count1 DESC

2、前一年的数据

SELECT * FROM table WHERE YEAR(create_time)=YEAR(DATE_SUB(NOW(),INTERVAL 1 YEAR))

3、本年或者某一年的数据

select * from table where date_format(create_time,'%Y') = date_format(now(),'%Y')

-- 本年数据
select * from table where year(create_time) = year(CURRENT_DATE) 
                        

-- 2022数据
select * from table where year(create_time) = 2022
                          

二、季度统计

1、上一季度

SELECT * FROM table 
WHERE QUARTER(create_time)=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) 
AND YEAR(create_time) = YEAR(NOW())

2、本季度数据

SELECT * FROM table 
WHERE QUARTER(create_time)=QUARTER(NOW())
AND YEAR(create_time) = YEAR(NOW())

三、月份统计

1、统计某一年十二个月份的数据（在多个表中）

             SELECT
                (SELECT DATE_FORMAT(now(), '%Y-1')) AS name4,
                ( SELECT ifnull(SUM(count),0) AS count
        FROM(
            SELECT DATE_FORMAT(a.time,'%Y-01') AS time, ifnull(count(*), 0)count
            FROM table1  a
            WHERE DATE_FORMAT(a.time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-01')
            UNION ALL
            SELECT DATE_FORMAT(b.time,'%Y-01')AS time, ifnull(count(*), 0) count
            FROM table2 b
            WHERE DATE_FORMAT(b.time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-01')
            ) c) AS count4

        UNION
            SELECT
                (SELECT DATE_FORMAT(now(), '%Y-2')) AS name4,
                ( SELECT ifnull(SUM(count),0) AS count
        FROM(
            SELECT DATE_FORMAT(a.time,'%Y-02') AS time, ifnull(count(*), 0)count
            FROM table1  a
            WHERE DATE_FORMAT(a.time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-02')
            UNION ALL
            SELECT DATE_FORMAT(b.time,'%Y-02')AS time, ifnull(count(*), 0) count
            FROM table2 b
            WHERE DATE_FORMAT(b.time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-02')
            ) c) AS count4
            .....................

2、查询本月数据或者某月的数据

select * from table where date_format(create_time,'%Y-%m') = date_format(now(),'%Y-%m')

-- 当前月数据
select * from table where year(create_time) = year(CURRENT_DATE) 
                          AND MONTH(create_time) = MONTH(CURRENT_DATE) 

-- 一月的数据
select * from table where year(create_time) = year(CURRENT_DATE) 
                          AND MONTH(create_time) = 01

3、查询上个月的数据

select * from table
where date_format(create_time, '%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m') 

四、天数统计

1、前一天的数据

select * from table
where 
date_format(create_time, '%Y-%m-%d') = date_format(DATE_SUB(curdate(), INTERVAL 1 DAY),'%Y-%m-%d') 

2、当天的数据

-- 当天数据
select * from table where DATE(create_time) = curdate()

-- 此时此刻。。。
select * from table where date_format(create_time) = date_format(now())

-- 查询本周（这里的本周是从上个星期的周日开始计算，到这个星期的周六。）
select * from table where YEARWEEK(date_format(create_time,'%Y-%m-%d')) = YEARWEEK(now())

-- 查询上一周
SELECT * FROM table WHERE WEEK(create_time)=WEEK(DATE_SUB(NOW(),INTERVAL 1 WEEK))
AND YEAR(create_time) = YEAR(NOW()) 

-- 近七天的数据
select * from table where date_sub(curdate(), interval 7 day) <= date(create_time)