一个查询汇总的问题,解决就给分
两个表:A(定单):
pkid,name
1         周三
2         张四
B(定单内容):
id,pkid,item,price
1       1       电视机   2000
2       1       空调       1500
3       1       冰箱       2170
4       2       冰箱       1540
要求汇总出用户的定单金额和定单项目,项目为多项的用 ", "分开.
如:查询结果应该为:
周三       电视机,空调,冰箱     5670
张四       冰箱                             1540

------解决方案--------------------
create function f_str(@pkid int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str= ' '
select @str=@str+item+ ', ' from b where pkid=@pkid
select @str=left(@str,len(@str)-1)
return @str
end

select name,dbo.f_str(pkid) as 'item ',t.price
from a
left join (select pkid,sum(price) as price from b group by pkid) t on a.pkid=t.pkid

------解决方案--------------------
if object_id( 'pubs..A ') is not null
drop table A
go
create table A(pkid varchar(10),name varchar(10))
insert into A(pkid,name) values( '1 ', '周三 ')
insert into A(pkid,name) values( '2 ', '张四 ')

if object_id( 'pubs..B ') is not null
drop table B
go

create table B(id int,pkid varchar(10),item varchar(10),price int)
insert into B(id,pkid,item,price) values(1, '1 ', '电视机 ', 2000)
insert into B(id,pkid,item,price) values(2, '1 ', '空调 ' , 1500)
insert into B(id,pkid,item,price) values(3, '1 ', '冰箱 ' , 2170)
insert into B(id,pkid,item,price) values(4, '2 ', '冰箱 ' , 1540)
go
if object_id( 'pubs..f_hb ') is not null
drop function f_hb
go

--创建一个合并的函数
create function f_hb(@pkid varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + ', ' + cast(item as varchar) from b where pkid = @pkid
set @str = right(@str , len(@str) - 1)
return(@str)
End
go

--调用自定义函数得到结果：
select t1.name , t3.item , t2.price from A t1 ,
(select pkid , sum(price) as price from b group by pkid) t2,
(select distinct pkid ,dbo.f_hb(pkid) as item from b) t3
where t1.pkid = t2.pkid and t1.pkid = t3.pkid

drop table A,B

/*
name item price
---------- ------------------- -----------
周三 电视机,空调,冰箱 5670
张四 冰箱 1540

（所影响的行数为 2 行）
*/