求一交叉表统计SQL ，紧急求助子莫等大虾
Create Table BOX(ID Varchar(10),BOXNO Varchar(10),Qty Int)
Insert BOX Select '001 ', 'A ',10
Union all Select '001 ', 'B ',20
Union all Select '001 ', 'C ',30
Union all Select '002 ', 'A ',20
Union all Select '002 ', 'B ',30
Union all Select '002 ', 'C ',40
Union all Select '003 ', 'B ',50
/*
想要的结果如下 表数据 box1 (ID,TOTALQTY,BOXSNO)
001,60,A\B\C
002,90,A\B\C
003,50,B
*/
[解决办法]
Create function uf_HBstr(@tid varchar(20))
Returns varchar(8000)
as
begin
declare @re varchar(8000)
Select @re=IsNULL(@re, ' ')+ '/ '+cast(BOXNO as varchar)
from box where ID=@tid
Return(Stuff(@re,1,1, ' '))
end
go
Select ID,sum(Qty) as TOTALQTY,dbo.uf_HBstr(ID) as BOXSNO
from box group by ID
[解决办法]
--select dinstinct 将重复数据剔除
Create Table BOX(ID Varchar(10),BOXNO Varchar(10),Qty Int)
Insert BOX Select '001 ', 'A ',10
Union all Select '001 ', 'B ',20
Union all Select '001 ', 'C ',30
Union all Select '001 ', 'C ',0
Union all Select '001 ', 'A ',30
Union all Select '002 ', 'A ',20
Union all Select '002 ', 'B ',30
Union all Select '002 ', 'C ',40
Union all Select '003 ', 'B ',50
go
create function f_hb(@ID int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ' '
select @str = @str + '/ ' + cast(boxno as varchar) from (select distinct id ,boxno from box) a where ID = @ID
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果：
select a.* , c.boxno from
(select id , sum(qty) as qty from box group by id) a,
(select id , dbo.f_hb(ID) as boxno from (select distinct id ,boxno from box) a group by id) c
where a.id = c.id
drop table box
drop function f_hb
--结果
id qty boxno
---------- ----------- -----
001 90 A/B/C
002 90 A/B/C
003 50 B
（所影响的行数为 3 行）
[解决办法]
此类问题用函数来解决比较方便
楼主吸收完了没有,放分吧
