现有一个数据表数据结构如下:
id item date qty
001 a 2009-1-1 10
002 b 2009-2-1 11
003 a 2009-2-1 12
004 b 2009-4-1 40
005 a 2009-5-1 20
006 c 2009-1-1 12
007 a 2009-3-1 15
008 c 2009-2-1 18
009 b 2009-3-1 40
010 a 2009-4-1 30怎么得出一定时间段内 a,b,c 出现的次数、id、date和qty
例如:2个月内。请高手帮忙想想办法,谢谢了!
id item date qty
001 a 2009-1-1 10
002 b 2009-2-1 11
003 a 2009-2-1 12
004 b 2009-4-1 40
005 a 2009-5-1 20
006 c 2009-1-1 12
007 a 2009-3-1 15
008 c 2009-2-1 18
009 b 2009-3-1 40
010 a 2009-4-1 30怎么得出一定时间段内 a,b,c 出现的次数、id、date和qty
例如:2个月内。请高手帮忙想想办法,谢谢了!
select item, sum(qty) as qty
from tb
where date between dateadd(month,-2,getdate()) and getdate()
group by item
select item, sum(qty) as qty
from tb
where date between dateadd(month,-2,getdate()) and getdate()
group by item
a.item,a.date,b.qty,b.次数
from
tb a,
(select item, sum(qty) as qty,count(1) as 次数 from tb group by item)b
where
a.item=b.item
a.item,a.date,b.qty,b.次数
from
tb a,
(select item, sum(qty) as qty,count(1) as 次数 from tb group by item)b
where
a.item=b.item
and
a.date between dateadd(month,-2,getdate()) and getdate()
,sum(case item when 'b' then 1 else 0 end) b
,sum(case item when 'c' then 1 else 0 end) c
from t
where DATEDIFF(month, getdate(),date ) =2