测试语句如下:
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
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事务日志
- 远程插入一条数据后,需要返回是否远程数据库成功保存吗?
- SQLServer登录问题
- sql 删除多表关联数据
- SQL Server 2005 Full Text Search如何得到term-document(tuple) matrix
- 请教,如何给表添加个序号字段自动流水号?
- 如何运用sql本地包把每个月的txt文件自动导入对应的月表中
- 在ESQL中如何执行存储过程?
- 用dts把sql server表转换成excel,为何一直累加数据?
- 用IP怎么连数据库?
- 请教一下,SQL Server 2000 为什么装不起来的问题?
- 请问SQL语句中应该过滤或替换掉哪些字符??
不过好像要嵌套几层,晕。楼主的意思应该是可以把他的查询语句做为子查询,说要用函数和存储过程的朋友们快点搞定啊
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
goselect 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