这个sql有什么问题？请赐教

[childbaby]

table1
a1 a2 id
1 1 1
1 2 2
1 2 3
2 3 4
2 4 5

table2
id b1
1 RMB
2 USD
3 HKG
4 RMB
5 RMB

我想得到一个结果，是
a1 sum(a2) b1
1 5 RMB/USD/HKG
2 7 RMB

[hyde100]

 
发表于：2008-10-29 10:59:201楼 得分:0 
SQL code问题描述：
无论是在sql 2000，还是在 sql 2005 中,都没有提供字符串的聚合函数，
  所以，当我们在处理下列要求时，会比较麻烦：
有表tb, 如下：
id    value
----- ------
1     aa
1     bb
2     aaa
2     bbb
2     ccc
需要得到结果：
id     values
------ -----------
1      aa,bb
2      aaa,bbb,ccc
即， group by id, 求 value 的和（字符串相加）

1. 旧的解决方法

-- 1. 创建处理函数
CREATE FUNCTION dbo.f_str(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @r varchar(8000)
    SET @r = ''
    SELECT @r = @r + ',' + value
    FROM tb
    WHERE id=@id
    RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数

SELECt id, values=dbo.f_str(id) 
FROM tb 
GROUP BY id

-- 2. 新的解决方法 
-- 示例数据
DECLARE @t TABLE(id int, value varchar(10))
INSERT @t SELECT 1, 'aa'
UNION ALL SELECT 1, 'bb'
UNION ALL SELECT 2, 'aaa'
UNION ALL SELECT 2, 'bbb'
UNION ALL SELECT 2, 'ccc'

-- 查询处理
SELECT *
FROM(
    SELECT DISTINCT 
        id
    FROM @t
)A
OUTER APPLY(
    SELECT 
        [values]= STUFF(REPLACE(REPLACE(
            (
                SELECT value FROM @t N
                WHERE id = A.id
                FOR XML AUTO
            ), '<N value="', ','), '"/>', ''), 1, 1, '')
)N

/*--结果
id          values
----------- ----------------
1           aa,bb
2           aaa,bbb,ccc
(2 行受影响)
--*/

CSDN 社区帖子地址 

附: 合并与分拆的CLR， sql2005的示例中有：
在安装sql 2005的示例后，默认安装目录为
drive:\Program Files\Microsoft SQL Server\90\Samples\Engine\Programmability\CLR\StringUtilities中
 
 

[blogz]

行列转换，很多的

查看历史帖子

[blogz]

说差了，分组聚合，很多很多。。。。

[blogz]

说差了，分组聚合，很多很多。。。。
你的要求也很大众化

[Yang_]

函数：

create function fn_b1(
@a1 int
)
returns varchar(100)
as
begin
   declare @r varchar(100)
   select @r=isnull(@r+',','')+table2.b1 from table2,table1 where table1.id=table2.id and table1.a1=@a1
   return @r
end

go

--查询
select a1,sum(a2) as [sum(a2)],dbo.fn_b1(a1) as b1
from table1
group by a1

[Yang_]

,换成/

[等不到来世]

2005:

if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([a1] int,[a2] int,[id] int)
insert [table1]
select 1,1,1 union all
select 1,2,2 union all
select 1,2,3 union all
select 2,3,4 union all
select 2,4,5
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([id] int,[b1] varchar(3))
insert [table2]
select 1,'rmb' union all
select 2,'usd' union all
select 3,'hkg' union all
select 4,'rmb' union all
select 5,'rmb'

--select * from [table1]
--select * from [table2]

select	a.a1,[sum(a2)]=sum(a.a2)
		,b1=stuff((select '/'+b1 from table2 where id in (select id from table1 where a1=a.a1) for xml path('')),1,1,'')
from table1 a join table2 b
on a.id=b.id
group by a.a1

--测试结果:
/*
a1          sum(a2)     b1
----------- ----------- ---------------
1           5           rmb/usd/hkg
2           7           rmb/rmb

(2 row(s) affected)
*/

[等不到来世]

改一下：

if object_id('[table1]') is not null drop table [table1]
go
create table [table1]([a1] int,[a2] int,[id] int)
insert [table1]
select 1,1,1 union all
select 1,2,2 union all
select 1,2,3 union all
select 2,3,4 union all
select 2,4,5
if object_id('[table2]') is not null drop table [table2]
go
create table [table2]([id] int,[b1] varchar(3))
insert [table2]
select 1,'rmb' union all
select 2,'usd' union all
select 3,'hkg' union all
select 4,'rmb' union all
select 5,'rmb'

--select * from [table1]
--select * from [table2]

select	a.a1,[sum(a2)]=sum(a.a2)
		,b1=stuff((select '/'+b1 from table2 where id in (select id from table1 where a1=a.a1) group by b1 for xml path('')),1,1,'')
from table1 a join table2 b
on a.id=b.id
group by a.a1

--测试结果:
/*
a1          sum(a2)     b1
----------- ----------- ---------------
1           5           rmb/usd/hkg
2           7           rmb

(2 row(s) affected)
*/

[surpass63]

查询历史贴

[ChinaJiaBing]

if object_id('table1')is not null
   drop table table1
if object_id('table2')is not null
    drop table table2
if object_id('f')is not null
    drop function f
go
create table table1(a1 int,a2 int,id int)
insert into table1 select 1,1,1
   union all select 1,2,2
   union all select 1,2,3
   union all select 2,3,4
   union all select 2,4,5
create table table2(id int,b1 varchar(3))
insert into table2 select 1,'rmb'
         union all select 2,'usd'
         union all select 3,'hkg'
         union all select 4,'rmb'
         union all select 5,'rmb'
go
create function f (@a1 int)
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=isnull(@str,'')+'/'+b1 from table1 join table2
on table1.id=table2.id
where a1=@a1
return stuff(@str,1,1,'')
end
go
select a1,dbo.f(a1) from table1 join table2 on table1.id=table2.id
group by a1

1	rmb/usd/hkg
2	rmb/rmb

[ChinaJiaBing]

if object_id('table1')is not null
   drop table table1
if object_id('table2')is not null
    drop table table2
if object_id('f')is not null
    drop function f
go
create table table1(a1 int,a2 int,id int)
insert into table1 select 1,1,1
   union all select 1,2,2
   union all select 1,2,3
   union all select 2,3,4
   union all select 2,4,5
create table table2(id int,b1 varchar(3))
insert into table2 select 1,'rmb'
         union all select 2,'usd'
         union all select 3,'hkg'
         union all select 4,'rmb'
         union all select 5,'rmb'
go
create function f (@a1 int)
returns varchar(100)
as
begin
declare @str varchar(100)
set @str=''
select @str=isnull(@str,'')+'/'+b1 from table1 join table2
on table1.id=table2.id
where a1=@a1
return stuff(@str,1,1,'')
end
go
select a1,'sum(a2)'=sum(a2),dbo.f(a1) as b1 from table1 join table2 on table1.id=table2.id
group by a1

[tiyuzhongxin789]

说差了，分组聚合，很多很多。。。。 你的要求也很大众化

[中国风]

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T1') is null
	drop table #T1
Go
Create table #T1([a1] int,[a2] int,[id] int)
Insert #T1
select 1,1,1 union all
select 1,2,2 union all
select 1,2,3 union all
select 2,3,4 union all
select 2,4,5
Go
--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T2') is null
	drop table #T2
Go
Create table #T2([id] int,[b1] nvarchar(3))
Insert #T2
select 1,N'RMB' union all
select 2,N'USD' union all
select 3,N'HKG' union all
select 4,N'RMB' union all
select 5,N'RMB'
Go
;with c1
as
(select t1.[a1],[a2]=sum(t1.[a2])over(partition by[a1] ),t2.[b1],
row=row_number()over(partition by t1.[a1]order by t1.[a1])
from  #T1 t1 join #T2 t2 on t1.[id]=t2.[id]
),c2 as
(select a1,a2,cast(b1 as nvarchar(100))b1,row from C1 where row=1 
union all 
select a.a1,a.a2,cast(b.b1+','+a.b1 as nvarchar(100)),a.row from C1 a join C2 b on a.a1=b.a1 and a.row=b.row+1)
select a1,a2,b1 from c2 a where row=(select max(row) from C1 where a1=a.a1) order by a1 option (MAXRECURSION 0)



(5 行受影响)

(5 行受影响)
a1          a2          b1
----------- ----------- ----------------------------------------------------------------------------------------------------
1           5           RMB,USD,HKG
2           7           RMB,RMB

(2 行受影响)