--> 测试数据: @a
declare @a table (a_date datetime,qtime varchar(5), a_log varchar(1),a_rs int,a_ys int)
insert into @a
select '2009-11-1','07:00','T',1,7 union all
select '2009-11-1','07:00','T',2,8 union all
select '2009-11-1','07:00','T',2,8 union all
select '2009-11-1','08:00','T',3,9 union all
select '2009-11-1','08:00','T',4,10 union all
select '2009-11-1','09:00','F',4,10select a_date,sum(b.a_rs) as a_rs,sum(b.a_ys) as a_ys,
bc1=sum(case when a_log='T' then 1 else 0 end ),
bc2=sum(case when a_log='F' then 1 else 0 end )
from (
select a_date,qtime,a_log,sum(a_rs) as a_rs ,sum(a_ys) as a_ys from @a group by a_date,qtime,a_log)b group by a_date,a_log
能否将以上语句优化一下?或者只用一个SELECT?
declare @a table (a_date datetime,qtime varchar(5), a_log varchar(1),a_rs int,a_ys int)
insert into @a
select '2009-11-1','07:00','T',1,7 union all
select '2009-11-1','07:00','T',2,8 union all
select '2009-11-1','07:00','T',2,8 union all
select '2009-11-1','08:00','T',3,9 union all
select '2009-11-1','08:00','T',4,10 union all
select '2009-11-1','09:00','F',4,10select a_date,sum(b.a_rs) as a_rs,sum(b.a_ys) as a_ys,
bc1=sum(case when a_log='T' then 1 else 0 end ),
bc2=sum(case when a_log='F' then 1 else 0 end )
from (
select a_date,qtime,a_log,sum(a_rs) as a_rs ,sum(a_ys) as a_ys from @a group by a_date,qtime,a_log)b group by a_date,a_log
能否将以上语句优化一下?或者只用一个SELECT?
a_date,
sum(case when a_log='T' then a_rs else 0 end) as a_rs,
sum(case when a_log='F' then a_ys else 0 end) as a_ys,
bc1=sum(case when a_log='T' then 1 else 0 end ),
bc2=sum(case when a_log='F' then 1 else 0 end )
from @a
group by a_date
insert into @a
select '2009-11-1','07:00','T',1,7 union all
select '2009-11-1','07:00','T',2,8 union all
select '2009-11-1','07:00','T',2,8 union all
select '2009-11-1','08:00','T',3,9 union all
select '2009-11-1','08:00','T',4,10 union all
select '2009-11-1','09:00','F',4,10select a_date,
a_rs=sum(a_rs),
a_ys=sum(a_ys),
bc1=(select count(distinct qtime) from @a where t.a_log=a_log and a_log='T'),
bc2=(select count(distinct qtime) from @a where t.a_log=a_log and a_log='F')
from @a t
group by a_date,a_log/*(所影响的行数为 6 行)a_date a_rs a_ys bc1 bc2
------------------------------------------------------ ----------- ----------- ----------- -----------
2009-11-01 00:00:00.000 4 10 0 1
2009-11-01 00:00:00.000 12 42 2 0(所影响的行数为 2 行)*/
a_date a_rs a_ys bc1 bc2
2009-11-01 16 52 2 1
declare @a table (a_date datetime,qtime varchar(5), a_log varchar(1),a_rs int,a_ys int)
insert into @a
select '2009-11-1','07:00','T',1,7 union all
select '2009-11-1','07:00','T',2,8 union all
select '2009-11-1','07:00','T',2,8 union all
select '2009-11-1','08:00','T',3,9 union all
select '2009-11-1','08:00','T',4,10 union all
select '2009-11-1','09:00','F',4,10select
a_date,
sum(a_rs) as a_rs,
sum(a_ys) as a_ys,
bc1=sum(case when a_log='T' then 1 else 0 end ),
bc2=sum(case when a_log='F' then 1 else 0 end )
from @a
group by a_date,a_log
/**
a_date a_rs a_ys bc1 bc2
------------------------------------------------------ ----------- ----------- ----------- -----------
2009-11-01 00:00:00.000 4 10 0 1
2009-11-01 00:00:00.000 12 42 5 0(所影响的行数为 2 行)
**/
a_date,
a_rs=sum(a_rs),
a_ys=sum(a_ys),
sum(case when a_log='T' then 1 else 0 end ) as bc1,
sum(case when a_log='F' then 1 else 0 end ) as bc2
from
@a
group by
a_date,a_log
a_date,
sum(a_rs) as a_rs,
sum(a_ys) as a_ys,
bc1=sum(case when a_log='T' then 1 else 0 end ),
bc2=sum(case when a_log='F' then 1 else 0 end )
from @a
group by a_date
/**
a_date a_rs a_ys bc1 bc2
------------------------------------------------------ ----------- ----------- ----------- -----------
2009-11-01 00:00:00.000 16 52 5 1(所影响的行数为 1 行)
**/
declare @a table (a_date datetime,qtime varchar(5), a_log varchar(1),a_rs int,a_ys int)
insert into @a
select '2009-11-1','07:00','T',1,7 union all
select '2009-11-1','07:00','T',2,8 union all
select '2009-11-1','07:00','T',2,8 union all
select '2009-11-1','08:00','T',3,9 union all
select '2009-11-1','08:00','T',4,10 union all
select '2009-11-1','09:00','F',4,10select a_date,
a_rs=sum(a_rs),
a_ys=sum(a_ys),
bc1=(select count(distinct qtime) from @a where a_log='T'),
bc2=(select count(distinct qtime) from @a where a_log='F')
from @a t
group by a_date/*
(所影响的行数为 6 行)a_date a_rs a_ys bc1 bc2
------------------------------------------------------ ----------- ----------- ----------- -----------
2009-11-01 00:00:00.000 16 52 2 1(所影响的行数为 1 行)
*/
insert into @a
select '2009-11-1','07:00','T',1,7 union all
select '2009-11-1','07:00','T',2,8 union all
select '2009-11-1','07:00','T',2,8 union all
select '2009-11-1','08:00','T',3,9 union all
select '2009-11-1','08:00','T',4,10 union all
select '2009-11-1','09:00','F',4,10 select a_date,
sum(a_rs) a_rs,
sum(a_ys) a_ys,
(select count(distinct qtime) from @a where a_log = 't' and a_date = t.a_date) bc1,
(select count(distinct qtime) from @a where a_log = 'f' and a_date = t.a_date) bc2
from @a t
group by a_date/*
a_date a_rs a_ys bc1 bc2
------------------------------------------------------ ----------- ----------- ----------- -----------
2009-11-01 00:00:00.000 16 52 2 1(所影响的行数为 1 行)
*/