关于生日在一个时间段内出现的次数，求高手，最简单的方法

Question

     遇到一个问题，有客户表（customer），数据量较大，含字段：唯一标识会员卡号（customersid）,注册时间（create_time），生日（birthday）。现在我要统计：会员注册之后截止至时间2011年4月30日的会员经历的生日次数。

Answer 1

select 会员卡号,次数=datediff(year,birthday,'2011-04-30')-
                    datediff(year,create_time,'2011-04-30')
from tb

Answer 2

--修改下select 会员卡号,次数=datediff(year,birthday,'2011-04-30')-
                    datediff(year,birthday,create_time)
from tb

Answer 3

select datediff(dd,birthday,'2011-04-30')/365+1

Answer 4

select customersid, year('2011-04-30') - year(create_time) -1 
+ case when convert(varchar(5), birthday, 101) >= convert(varchar(5), create_time, 101) then 1 else 0 end
+ case when convert(varchar(5), birthday, 101) <= convert(varchar(5), cast('2011-04-30' as datetime), 101) then 1 else 0 end
from customer

Answer 5

select customersid,
case when birthday > '2011-04-30' or '2011-04-30' < create_time then 0 --截止日期小于注册日期，或者生日在截止日期之后，则为0
else 
--1、中间年份有多少年则过几次生日（从生日或者注册日期中年份较晚的那个算起）
 year('2011-04-30') - case when year(create_time) < year(birthday) then year(birthday) else year(create_time) end -1  
--2、注册那年有没有过生日
+ case when convert(varchar(5), birthday, 101) >= convert(varchar(5), create_time, 101) then 1 else 0 end
--3、截止日期那年有没有过生日
+ case when convert(varchar(5), birthday, 101) <= convert(varchar(5), cast('2011-04-30' as datetime), 101) then 1 else 0 end 
end as 生日次数
from customer

Answer 6

考虑全排列~declare @regdate datetime,@birthday datetime,@todate datetimeset @regdate='2010-04-29'
set @birthday='1988-04-30'
set @todate='2011-04-30'
select case 
--a 注册日期 b 生日 c 给定日期
--abc
when right(convert(varchar(10),@birthday,120),5)>=right(convert(varchar(10),@regdate,120),5) and right(convert(varchar(10),@birthday,120),5)<=right(convert(varchar(10),@todate,120),5) then datediff(year,@regdate,@todate)+1
--bac
when right(convert(varchar(10),@birthday,120),5)<=right(convert(varchar(10),@regdate,120),5) and right(convert(varchar(10),@regdate,120),5)<right(convert(varchar(10),@todate,120),5) then datediff(year,@regdate,@todate)
--acb
when right(convert(varchar(10),@regdate,120),5)<right(convert(varchar(10),@todate,120),5) and right(convert(varchar(10),@birthday,120),5)>=right(convert(varchar(10),@todate,120),5) and datename(year,@regdate)<>datename(year,@todate) then datediff(year,@regdate,@todate)
--bca
when right(convert(varchar(10),@birthday,120),5)<=right(convert(varchar(10),@todate,120),5) and right(convert(varchar(10),@todate,120),5)<right(convert(varchar(10),@regdate,120),5)  and datename(year,@regdate)<>datename(year,@todate) then datediff(year,@regdate,@todate)
--cab
when right(convert(varchar(10),@todate,120),5)<right(convert(varchar(10),@regdate,120),5) and right(convert(varchar(10),@regdate,120),5)<=right(convert(varchar(10),@birthday,120),5) and datename(year,@regdate)<>datename(year,@todate) then datediff(year,@regdate,@todate)
--cba
when right(convert(varchar(10),@todate,120),5)<=right(convert(varchar(10),@birthday,120),5) and right(convert(varchar(10),@birthday,120),5)<=right(convert(varchar(10),@regdate,120),5) and datename(year,@regdate)<>datename(year,@todate) then datediff(year,@regdate,@todate)-1
else 0 end 

Answer 7

只是举个例子，我需要判断customer表（大约数据50W）每个顾客的加入会员后过的生日次数，那运行起来岂不是很慢？

Answer 8

--方法一
with cte as
(
select a.*,dt=dateadd(yy,b.number,a.birthday) 
from customer a, master..spt_values b 
where b.type='P' and b.number between year(create_time)-year(birthday) and 2011-year(birthday)
)
select customerid,count(*) from cte where dt between create_time and '2011-04-01' group by customerid--方法二
select *,cnt=datediff(yy,create_time,'2011-04-01')
-case when dateadd(yy,2011-year(birthday),birthday)>=dateadd(yy,2011-year(create_time),create_time) then 0 else 1 end
+case when dateadd(yy,2011-year(birthday),birthday)>'2011-04-01' then 0 else 1 end
from customer