问个通俗易懂的分段查询问题

[ntmdbya]

表 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-５　到 2009-2-４　和 2009-2-5　到 2009-3-4 各类别到期总数

也就是
chanpin 一月到期数　　　二月到期数
A 2 1
B 2 0
C 1 0
E 0 1

[sdhdy]

select chanpin,一月到期数=sum(case when youxiaoqi between '2009-1-5' and '2009-2-4' then 1 else 0 end),
二月到期数=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

[ak8800]

select count(*) from TBName where youxiaoqi between '2009-1-5' and '2009-2-4' or youxiaoqi between '2009-2-5' and '2009-3-4' group by chanpin

[-晴天]

create table tb(chanpin varchar(10),youxiaoqi datetime)
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
*/

[ntmdbya]

这么多没有吃饭的兄弟，马上结贴~

[長胸為富]

select chanpin,(select count(*) from info where chanpin = t.chanpin and youxiaoqi between '2009-1-5' and '2009-2-4'),
(select count(*) from info where chanpin = t.chanpin and youxiaoqi between '2009-2-4' and '2009-2-5')
from info t
group by chanpin