T1
日期 类别
2008-11-12 A
2008-11-12 A
2008-11-13 A
2008-11-13 A
2008-11-12 B
2008-11-13 B
2008-11-12 B
结果
日期 A类记录数 B类记录数
2008-11-12 2 2
2008-11-13 2 1
create table T1
(日期 datetime,type varchar(2))
insert into T1(日期,type)
select '2008-11-13','A'union
select '2008-11-13' ,'A' union
select '2008-11-12' ,'A' union
select '2008-11-12' ,'A' union
select '2008-11-13' ,'B' union
select '2008-11-13' ,'B'union
select '2008-11-12' ,'B'
--运行结果所用代码如下:
select a1.日期,a1.a as A类记录数,a2.b as B类记录数 from T1 ,
(select count(*) as A,CONVERT(varchar(100), 日期, 23)as 日期 from t1 where type='A' group by 日期)as a1,
(select count(*) as B,CONVERT(varchar(100), 日期, 23)as 日期 from t1 where type='B' group by 日期) as a2 where a1.日期=a2.日期 group by a1.日期,a1.a,a2.b