SQL Server 2008:计算多个日期范围中的键数
问题描述:
SQL Server 2008再次。我有一个主键pt_id
和日期时间列order_dts
为每个ID。我想从1996年到2008年每年计算一次密钥的数量,并返回8次结果 - 每年计数一次。对于一个单一的一年SQL Server 2008:计算多个日期范围中的键数
SELECT COUNT pm.pt_id AS '1996'
FROM dm.medications pm
WHERE (pm.order_dts BETWEEN '1/1/1996' and '12/31/1996')
的作品,但我我该怎么办所有的年,而不必做一些可笑的像八个不同的查询?
谢谢!
答
SELECT COUNT(*), Year(pm.order_dts) as Year
FROM dm.medications pm
WHERE Year(pm.order_dts) between 1996 AND 2008
group by Year(pm.order_dts)
如果你想这一切在一排,你可以这样做:
SELECT
sum(case when Year(pm.order_dts) = 1996 then 1 end) as Count1996,
sum(case when Year(pm.order_dts) = 1997 then 1 end) as Count1997,
sum(case when Year(pm.order_dts) = 1998 then 1 end) as Count1998,
sum(case when Year(pm.order_dts) = 1999 then 1 end) as Count1999,
sum(case when Year(pm.order_dts) = 2000 then 1 end) as Count2000,
sum(case when Year(pm.order_dts) = 2001 then 1 end) as Count2001,
sum(case when Year(pm.order_dts) = 2002 then 1 end) as Count2002,
sum(case when Year(pm.order_dts) = 2003 then 1 end) as Count2003,
sum(case when Year(pm.order_dts) = 2004 then 1 end) as Count2004,
sum(case when Year(pm.order_dts) = 2005 then 1 end) as Count2005,
sum(case when Year(pm.order_dts) = 2006 then 1 end) as Count2006,
sum(case when Year(pm.order_dts) = 2007 then 1 end) as Count2007,
sum(case when Year(pm.order_dts) = 2008 then 1 end) as Count2008
FROM dm.medications pm
答
select count(pm.pt_id) as count, datepart(yy, pm.order_dts) as year
from dm.medications pm
WHERE (pm.order_dts BETWEEN '1/1/1996' and '12/31/1996')
group by datepart(yy, pm.order_dts)
@OrbMan:我已经添加了条件限制的记录为每年的标准。 – shahkalpesh 2010-01-18 16:57:03
我总是忘记YEAR功能。 – Joe 2010-01-18 16:58:46
@shahkalpesh:谢谢! – RedFilter 2010-01-18 17:01:25