SQL 面试题 XML PATH

0
sql求解
表a
列 a1 a2
记录 1 a
1 b
2 x
2 y
2 z
用select能选成以下结果吗？
1 ab
2 xyz
 
我的解法--用STUFF和XML PATH：
CREATE TABLE a

(ID INT,

 Name NVARCHAR(5))



INSERT INTO a

VALUES

(1,'a'),

(1,'b'),

(2,'x'),

(2,'y'),

(2,'z');



SELECT ID, Name = STUFF(

                        (SELECT ' ' + Name

                         FROM [dbo].[a] AS a2

                         WHERE a2.ID = a.ID

                         ORDER BY ID

                         FOR XML PATH('')

						)

						,1,1,'')

                      FROM [dbo].[a] AS a

GROUP BY ID

ORDER BY ID;
执行后的结果：

w13.jpg

 
面试题目要求格式
1 ab
2 xyz
 
请问字母之间的空格如何去掉？
2016-05-26 添加评论
分享

没有找到相关结果

    已邀请:
    0

    悟 - 取是能力，舍是境界 2016-05-26 回答 


    
SELECT ID, Name = REPLACE(STUFF(
    
                        (SELECT ' '+Name
    
                         FROM [dbo].[a] AS a2
    
                         WHERE a2.ID = a.ID
    
                         ORDER BY ID
    
                         FOR XML PATH('')
    
						)
    
						,1,1,''),' ','')
    
                      FROM [dbo].[a] AS a
    
GROUP BY ID
    
ORDER BY ID;
    最简单的办法是再加个Replace函数
    2016-05-26 0
    分享
    1

    wenti 2016-05-26 回答

    谢谢解答！
     
    附加一个方法：
    SELECT ID, Name = (SELECT ''+Name --Use empty string instead of space here
    
                     FROM [dbo].[a] AS a2
    
                     WHERE a2.ID = a.ID
    
                     ORDER BY ID
    
                     FOR XML PATH(''))
    
FROM [dbo].[a] AS a
    
GROUP BY ID ORDER BY ID;
    2016-05-26 1
    分享
    为什么被折叠? 0 个回复被折叠

    要回复问题请先登录或注册