select convert(char(10),fd1) as fddate,
count(case when fd2 like '%aa%' then fd2 else 0 ) as aa,
count(case when fd2 like '%bb%' then fd2 else 0 ) as bb,
count(case when fd2 like '%cc%' then fd2 else 0 ) as cc,
count(case when fd2 like '%dd%' then fd2 else 0 ) as dd,
count(case when fd2 like '%ee%' then fd2 else 0 ) as ee,
from tb1
group by fd1
order by fd1
count(case when fd2 like '%aa%' then fd2 else 0 ) as aa,
count(case when fd2 like '%bb%' then fd2 else 0 ) as bb,
count(case when fd2 like '%cc%' then fd2 else 0 ) as cc,
count(case when fd2 like '%dd%' then fd2 else 0 ) as dd,
count(case when fd2 like '%ee%' then fd2 else 0 ) as ee,
from tb1
group by fd1
order by fd1
sum(case when fd2 like '%,aa,%' then 1 else 0 end) as aa,
sum(case when fd2 like '%,bb,%' then 1 else 0 end) as bb,
sum(case when fd2 like '%,cc,%' then 1 else 0 end) as cc,
sum(case when fd2 like '%,dd,%' then 1 else 0 end) as dd,
sum(case when fd2 like '%,ee,%' then 1 else 0 end) as ee
from tb1
group by convert(char(10),fd1,120)
order by fddate
create table tb1(fd1 datetime,fd2 varchar(50))
insert tb1 values ('2004-08-01 12:00', ',aa,bb,cc,')
insert tb1 values ('2004-08-01 12:51', ',aa,bb,dd,')
insert tb1 values ('2004-08-02 13:22', ',aa,bb,')
insert tb1 values ('2004-08-02 10:11', ',aa,bb,cc,dd,ee')
insert tb1 values ('2004-08-03 13:18', ',aa,bb,cc,ee,')
insert tb1 values ('2004-08-04 12:55', ',aa,')select convert(varchar(10),fd1,120) as fddate,
aa=count(case when fd2 like '%aa%' then 1 end),
bb=count(case when fd2 like '%bb%' then 1 end),
cc=count(case when fd2 like '%cc%' then 1 end),
dd=count(case when fd2 like '%dd%' then 1 end),
ee=count(case when fd2 like '%ee%' then 1 end)
into #tb1
from tb1
group by fd1select fddate,aa=sum(aa),bb=sum(bb),cc=sum(cc),dd=sum(dd),ee=sum(ee)
from #tb1
group by fddate
drop table tb1,#tb1
--结果
fddate aa bb cc dd ee
---------- ----------- ----------- ----------- ----------- -----------
2004-08-01 2 2 1 1 0
2004-08-02 2 2 1 1 1
2004-08-03 1 1 1 0 1
2004-08-04 1 0 0 0 0(所影响的行数为 4 行)
insert into fd values('2004-08-02 13:22',',aa,bb,')
insert into fd values('2004-08-01 12:51',',aa,bb,dd,')
insert into fd values('2004-08-03 13:18',',aa,bb,cc,ee,')
insert into fd values('2004-08-04 12:55',',aa,')
insert into fd values('2004-08-02 10:11',',aa,bb,cc,dd,ee')select fddate,aa=sum(aa),bb=sum(bb),cc=sum(cc),dd=sum(dd),ee=sum(ee)
from
(select convert(char(10),fd1,120) as fddate,
sum(case when fd2 like '%aa%' then 1 else 0 end) as aa,
sum(case when fd2 like '%bb%' then 1 else 0 end) as bb,
sum(case when fd2 like '%cc%' then 1 else 0 end) as cc,
sum(case when fd2 like '%dd%' then 1 else 0 end) as dd,
sum(case when fd2 like '%ee%' then 1 else 0 end) as ee
from fd
group by convert(char(10),fd1,120)
) as tab
group by fddate
結果和樓上一樣的,
原始表tb1
fd1 fd2
2004-08-02 10:11 ,aa,bb,cc,dd,ee
2004-08-01 12:00 ,aa,bb,cc,
2004-08-01 12:51 ,aa,bb,dd,
2004-08-03 13:18 ,aa,bb,cc,ee,
2004-08-04 12:55 ,aa,
2004-08-02 13:22 ,aa,bb,时段报表
fdtime aa bb cc dd ee
08:00-12:00 2 2 2 1 1
12:00-16:00 4 3 1 0 1
时段的意思是,所有时间在该时段的个数
如08:00-12:00是指原始表的2004-08-02 10:11和2004-08-01 12:00两个记录的值做统计.
create table tb1(fd1 datetime,fd2 varchar(50))
insert tb1 values ('2004-08-02 10:11', ',aa,bb,cc,dd,ee')
insert tb1 values ('2004-08-01 12:00', ',aa,bb,cc,')
insert tb1 values ('2004-08-01 12:51', ',aa,bb,dd,')
insert tb1 values ('2004-08-03 13:18', ',aa,bb,cc,ee,')
insert tb1 values ('2004-08-04 12:55', ',aa,')
insert tb1 values ('2004-08-02 13:22 ', ',aa,bb,')select convert(varchar(5),fd1,114) as fdtime,
sum(case when fd2 like '%aa%' then 1 else 0 end) as aa,
sum(case when fd2 like '%bb%' then 1 else 0 end) as bb,
sum(case when fd2 like '%cc%' then 1 else 0 end) as cc,
sum(case when fd2 like '%dd%' then 1 else 0 end) as dd,
sum(case when fd2 like '%ee%' then 1 else 0 end) as ee
into #tb1
from tb1
group by convert(varchar(5),fd1,114)
order by fdtimeselect fdtime='08:00-12:00',aa=sum(aa),bb=sum(bb),cc=sum(cc),dd=sum(dd),ee=sum(ee)
from #tb1
where fdtime between '08:00'and '12:00'
union all
select fdtime='12:00-16:00',aa=sum(aa),bb=sum(bb),cc=sum(cc),dd=sum(dd),ee=sum(ee)
from #tb1
where fdtime > '12:00'and fdtime<'16:00'drop table tb1,#tb1
--结果:fdtime aa bb cc dd ee
----------- ----------- ----------- ----------- ----------- -----------
08:00-12:00 2 2 2 1 1
12:00-16:00 4 3 1 1 1(所影响的行数为 2 行)