22,206
社区成员
发帖
与我相关
我的任务
分享
SELECT sName,
(SELECT hobby+',' FROM student
WHERE sName=A.sName
FOR XML PATH('')) AS StuList
FROM student A
GROUP BY sName
--测试数据
if not object_id(N'student') is null
drop table student
Go
Create table student([sName] nvarchar(22),[hobby] nvarchar(22))
Insert student
select N'张三',N'爬山' union all
select N'张三',N'游泳' union all
select N'李四',N'美食' union all
select N'李四',N'美食' union all
select N'王五',N'爬山' union all
select N'王五',N'游泳' union all
select null,N'吃饭' union all
select null,N'睡觉'
Go
--测试数据结束
SELECT sName,
(SELECT hobby+',' FROM student
WHERE sName=A.sName OR (Sname IS NULL AND A.sName IS NULL)
FOR XML PATH('')) AS StuList
FROM student A
GROUP BY sName
NULL 吃饭,睡觉,
李四 美食,美食,
王五 爬山,游泳,
张三 爬山,游泳,
SELECT sName,
(SELECT hobby+',' FROM #tab
WHERE isnull(sName,'aaa')=isnull(A.sName,'aaa')
FOR XML PATH('')) AS StuList
FROM #tab A
GROUP BY sName
加个isnull就可以了,随便写个isnull什么DECLARE @t TABLE (stuid INT,sName NVARCHAR(10),hobby NVARCHAR(10))
INSERT INTO @t VALUES (1,'张三','爬山')
INSERT INTO @t VALUES (1,'张三','游泳')
INSERT INTO @t VALUES (2,'李四','美食')
INSERT INTO @t VALUES (3,'李四','美食')
INSERT INTO @t VALUES (4,'王五','爬山')
INSERT INTO @t VALUES (5,'王五','游泳')
INSERT INTO @t VALUES (0,NULL,'吃饭')
INSERT INTO @t VALUES (0,NULL,'睡觉')
SELECT isnull(sName,'') AS sName,
(SELECT hobby+',' FROM @t
WHERE ISNULL(sName,'')= ISNULL(A.sName,'')
FOR XML PATH('')) AS StuList
FROM @t A
GROUP BY isnull(sName,'')
/*
sName StuList
吃饭,睡觉,
李四 美食,美食,
王五 爬山,游泳,
张三 爬山,游泳,
*/