SQL语句中怎样进行组合查询？

Question

发表于：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中

Answer 1

SET NOCOUNT ON
if object_id('PERSONNEL')is not null drop table PERSONNEL
go
create table PERSONNEL (P_ID varchar(10),    P_NAME  nvarchar(5))
insert PERSONNEL select '0001',    N'李丽' 
if object_id('DATA')is not null drop table DATA
go
create table DATA(D_ID varchar(10),    D_NAME nvarchar(10)) 
insert DATA select '0001'  ,  N'策划' 
insert DATA select '0002'  ,  N'设计' 
insert DATA select '0003'  ,  N'统计' 
insert DATA select '0004'  ,  N'审核' 
if object_id('COMPETENCY')is not null drop table COMPETENCY
go
CREATE TABLE COMPETENCY(P_ID VARCHAR(5) ,   D_ID VARCHAR(5)) 
INSERT COMPETENCY SELECT '0001'  ,  '0001' 
INSERT COMPETENCY SELECT '0001'  ,  '0002' 
INSERT COMPETENCY SELECT '0001'  ,  '0003' 
INSERT COMPETENCY SELECT '0001'  ,  '0004' 
IF OBJECT_ID('dbo.f_str') IS NOT NULL DROP FUNCTION dbo.f_str
GO
CREATE FUNCTION dbo.f_str(@Pid VARCHAR(10))
RETURNS Nvarchar(4000)
AS
BEGIN
    DECLARE @r Nvarchar(4000)
    SET @r = ''
    SELECT @r = @r + ',' + b.D_NAME
    FROM PERSONNEL a left join COMPETENCY c on a.P_ID=c.P_ID left join DATA b on b.D_ID=c.D_ID 
    AND  a.P_ID=@Pid
    RETURN STUFF(@r, 1, 1, '')
END
GOselect a.P_ID,A.P_NAME,DBO.F_STR(A.P_ID) from PERSONNEL a left join COMPETENCY c on a.P_ID=c.P_ID left join DATA b on b.D_ID=c.D_ID GROUP BY A.P_ID,A.p_NAME
/*P_ID       P_NAME                                                                                                                                                                                                          
---------- ------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
0001       李丽     策划,设计,统计,审核
*/

Answer 2

你要的这个效果，用 MS-SQL是不行的，用 MySQL确可以，
MS-SQL 没有实现 类似于MySQL 的Group_Concat()函数的功能。
如果有，这个函数可能就得需要楼主自己写了。

Answer 3

先创建一个连接字符串的函数  unionData ,代码如下:
create function unionData (@P_ID varchar(500)) returns varchar(1000)
begin
    declare @varData varchar(1000)
    declare @getData varchar(1000)
    set @varData=''
    declare GetD_Name cursor for (select t1.D_Name from COMPETENCY  t0
    left outer join DATA  t1 on t1.D_ID=t0.D_ID where t0.P_ID=@P_ID)
    open GetD_Name
    fetch next  from GetD_Name into @getData
    while @@fetch_status=0 
    begin 
        print @getData
        if @varData=''
          begin
           set  @varData=@varData+@getData
          end
        else
           set  @varData=@varData+','+@getData
        print @varData
        fetch next  from GetD_Name into @getData     
    end 
    print @varData
    close GetD_Name
    deallocate GetD_Name
    return @varData
end 
然后用下面的代码就可以了:select A.P_ID,A.P_Name,dbo.unionData(A.P_ID) as D_Name from PERSONNEL  A
结果如下:
0001 李丽 策划,设计,统计,审核