sql

SQL指定多个日期数据查询

技术分享

systemime
2020-11-05
3 min

摘要.

# 较少日期指定

		SELECT l.`x`, l.`xxx`
    FROM `xxx` AS `l`
    WHERE l.`xxx` in ({})
        AND ......
        AND (
          ( datediff(`l`.`update_time` ,'2020-07-01') = 0 ) OR
          ( datediff(`l`.`update_time` ,'2020-xx-xx') = 0 ) OR
          ( datediff(`l`.`update_time` ,'2020-xx-xx') = 0 )
          ...可以拼接SQL
        )
    ORDER BY
        l.`id` DESC

# 较大时间跨度

		SELECT l.`x`, l.`xxx`
    FROM `xxx` AS `l`
    WHERE l.`xxx` in ({})
        AND ......
        AND (`l`.`update_time` >= 'xxxx-xx-xx' and `l`.`update_time` < 'xxxx-xx-xx')
        AND ......辅助其他条件
    ORDER BY
        l.`id` DESC

# sql server 查询某个时间段共有多少周及每周的日期段

select number+1 as wknum,dateadd(wk,number,2017-01-01) as firstday,
(case when year(dateadd(d,-1,dateadd(wk,number+1,2017-01-01)))>2017 then2017-12-31else dateadd(d,-1,dateadd(wk,number+1,2017-01-01)) end) as sevenday
from master..spt_values
where type=‘p‘ and year(dateadd(wk,number,2017-01-01))=2017

# 查询指定年份每个月不同日期数据

SELECT
	sum( CASE WHEN MONTH ( create_time )='1' and DAY(create_time)=1 THEN amount ELSE 0 END ) AS Jan,
	sum( CASE MONTH ( create_time ) WHEN '2' THEN amount ELSE 0 END ) AS Feb,
	sum( CASE MONTH ( create_time ) WHEN '3' THEN amount ELSE 0 END ) AS Mar,
	sum( CASE MONTH ( create_time ) WHEN '4' THEN amount ELSE 0 END ) AS Apr,
	sum( CASE MONTH ( create_time ) WHEN '5' THEN amount ELSE 0 END ) AS May,
	sum( CASE MONTH ( create_time ) WHEN '6' THEN amount ELSE 0 END ) AS June,
	sum( CASE MONTH ( create_time ) WHEN '7' THEN amount ELSE 0 END ) AS July,
	sum( CASE MONTH ( create_time ) WHEN '8' THEN amount ELSE 0 END ) AS Aug,
	sum( CASE MONTH ( create_time ) WHEN '9' THEN amount ELSE 0 END ) AS Sept,
	sum( CASE WHEN MONTH ( create_time )='10' and DAY(create_time)='11' THEN amount ELSE 0 END ) AS Oct
	sum( CASE MONTH ( create_time ) WHEN '11' THEN amount ELSE 0 END ) AS Nov,
	sum( CASE MONTH ( create_time ) WHEN '12' THEN amount ELSE 0 END ) AS Dece 
FROM
	`xxxxxx` 
WHERE
	YEAR ( create_time )= '2017';
SELECT
	sum( CASE WHEN YEAR(create_time) = '2019' AND MONTH ( create_time )= '10' AND DAY ( create_time )= '11' THEN amount ELSE 0 END ) AS Oct 
FROM
	`xxxxx` 
WHERE
	type IN ( 'xxx', 'xxxx' );
上次编辑于: 2021/5/24 下午1:55:49