Sql 语句小课堂5：日期的计算及应用

需求1，按周统计每周注册的用户数量

;with t as (
	select min(u_reg) as 最早注册时间,datepart(w,min(u_reg)) as 周几 
	from pub_users with (nolock) 
	where u_reg>'2000-1-1'
),t1 as (
	select *
		,dateadd(d,(case when 周几=1 then -6 else 2-周几 end),convert(date,最早注册时间)) as 周一 
	from t
),t2 as (
	select datediff(d,周一,u_reg)/7+1 as 周目,count(0) as 注册量 
	from pub_users u with (nolock),t1 b 
	where u_reg>=周一
	group by datediff(d,周一,u_reg)/7
)
select t2.*,dateadd(d,(周目-1)*7,周一),dateadd(d,周目*7-1,周一) from t2,t1 order by 1

为了符合国内的习惯，周一为每周第一天，所以要计算出周一的日期来，然后根据日期与第一个周日的日期差比7之后的数字就得到周目，按照周目统计即可得到每周统计数，最后输出时，将当前周目的周一和周日日期列出

需求2，对于注册用户，自注册日起，每个自然月发布的产品进行统计，注，自然月第一天从注册日计算，即：3月9日注册的，3月9日至4月8日为第一个自然月

;with t as (
	select * from users a
	cross apply(
		select number
		from master..spt_values
		where type='p' and number<=datediff(m,a.reg_date,getdate())
	) b
	cross apply(
		select dateadd(m,number,reg_date) as month_start
		,dateadd(d,-1,dateadd(m,number+1,reg_date)) as month_end
		where dateadd(m,number,reg_date)<=getdate()
	) c
)
select uid,month_start,month_end,cnt 
from t a
cross apply(
	select count(0) as cnt,sum(case when isDel=1 then 1 else 0 end) as 删除数
		,sum(case when isDel=0 and isOnline=1 then 1 else 0 end) as 上线数 
	from products with (nolock)
	where u_id=a.uid and createTime>=month_start and convert(date,createTime)<=month_end
) b
order by a.uid,month_start

自然月的计算还是用dateadd方式吧，年也一样，否则闰年，大月、小月能弄死人的，dateadd(m,1,'2019-1-30')直接得到的数据是2019-2-28，很方便哦，不需要在考虑自然月有几天的问题

直接根据注册时间与当前日期的月份差得出每个月的开始和结束日期，然后直接cross统计数据就可以了