求一个SELECT语句,谢谢!
测试语句如下:
if object_id( 'tempdb..#ss ')> 0 drop table #ss
create table #ss
(
a_id int ,
day datetime ,
h_1 varchar(20),
h_1_h numeric(6,3),
h_2 varchar(20),
h_2_h numeric(6,3),
h_3 varchar(20),
h_3_h numeric(6,3),
)
insert into #ss select 1, '2007-07-02 ', 'A ',0.125, 'B ',0.125, 'D ',0.125
insert into #ss select 1, '2007-07-02 ', 'C ',0.125, 'B ',0.125, 'D ',0.125
insert into #ss select 1, '2007-07-02 ', 'C ',0.5,null,null,null,null
insert into #ss(a_id,day) select 2, '2007-07-01 '
select a_id,day,h_1,sum(h_1_h) h_1_h
from (
select a_id,day,h_1,h_1_h from #ss union all
select a_id,day,h_2,h_2_h from #ss union all
select a_id,day,h_3,h_3_h from #ss
) s
group by a_id,day,h_1
1 2007-07-02 00:00:00.000 A .125
1 2007-07-02 00:00:00.000 B .250
1 2007-07-02 00:00:00.000 C .625
1 2007-07-02 00:00:00.000 D .250
2 2007-07-01 00:00:00.000 NULL NULL
现在我要对查询出来的结果再次以a_id,day作为group by 汇总,结果象下面这样是我想要的,这样的一个SELECT 语句怎么查询 .
1 2007-07-02 00:00:00.000 A:0.125;B:0.250;C:0.625;D:0.250
2 2007-07-01 00:00:00.000 NULL
[解决办法]
路过,学习一下...
[解决办法]
这样的结果，用存储过程处理
[解决办法]
用嵌套的动态sql，好像可以。
不过好像要嵌套几层，晕。
楼主的意思应该是可以把他的查询语句做为子查询，说要用函数和存储过程的朋友们快点搞定啊
[解决办法]
搞了半个小时,没弄出来,晕死,我明天想想办法,今天累了.先睡了
[解决办法]
create function testfun(@code varchar(100))
returns varchar(100)
begin
declare @str varchar(400)
declare @str1 varchar(50)
declare @str2 varchar(50)
set @str= ' '
set @str1= ' '
set @str2= ' '
declare c_test cursor for
select isnull(h_1,0),isnull(h_1_h,0) from(select a_id,day,h_1,sum(h_1_h) h_1_h
from (
select a_id,day,h_1,h_1_h from ss union all
select a_id,day,h_2,h_2_h from ss union all
select a_id,day,h_3,h_3_h from ss
) s
group by a_id,day,h_1) as t where a_id=@code
open c_test
FETCH NEXT FROM c_test INTO @str1,@str2
while @@FETCH_STATUS=0
begin
set @str=@str+(@str1+ ', '+@str2+ '; ')
FETCH NEXT FROM c_test INTO @str1,@str2
end
close c_test
return @str
end
go
select a_id,day,dbo.testfun(a_id) from (select a_id,day,h_1,sum(h_1_h) h_1_h
from (
select a_id,day,h_1,h_1_h from ss union all
select a_id,day,h_2,h_2_h from ss union all
select a_id,day,h_3,h_3_h from ss
) s
group by a_id,day,h_1 ) as t group by a_id,day
