表 info chanpin youxiaoqi
A 2009-1-4
A 2009-1-8
B 2009-1-9
A 2009-2-4
A 2009-3-3
C 2009-2-4
E 2009-3-1
B 2009-1-4
想求出 2009-1-5 到 2009-2-4 和 2009-2-5 到 2009-3-4 各类别到期总数也就是
chanpin 一月到期数 二月到期数
A 2 1
B 2 0
C 1 0
E 0 1
A 2009-1-4
A 2009-1-8
B 2009-1-9
A 2009-2-4
A 2009-3-3
C 2009-2-4
E 2009-3-1
B 2009-1-4
想求出 2009-1-5 到 2009-2-4 和 2009-2-5 到 2009-3-4 各类别到期总数也就是
chanpin 一月到期数 二月到期数
A 2 1
B 2 0
C 1 0
E 0 1
二月到期数=sum(case when youxiaoqi between '2009-2-5' and '2009-3-4' then 1 else 0 end)
from info group by chanpin order by chanpin
insert into tb select 'A','2009-1-4'
insert into tb select 'A','2009-1-8'
insert into tb select 'B','2009-1-9'
insert into tb select 'A','2009-2-4'
insert into tb select 'A','2009-3-3'
insert into tb select 'C','2009-2-4'
insert into tb select 'E','2009-3-1'
insert into tb select 'B','2009-1-4'
go
select chanpin,
sum(case when youxiaoqi between '2009-1-5' and '2009-2-4' then 1 else 0 end) as 一月到期数,
sum(case when youxiaoqi between '2009-2-5' and '2009-3-4' then 1 else 0 end) as 二月到期数
from tb group by chanpin
go
drop table tb
/*
chanpin 一月到期数 二月到期数
---------- ----------- -----------
A 2 1
B 1 0
C 1 0
E 0 1
*/
(select count(*) from info where chanpin = t.chanpin and youxiaoqi between '2009-2-4' and '2009-2-5')
from info t
group by chanpin