SqlServer Stuff用法

查询数据的时候经常需要将某列多行合并显示到一列中

这里模拟下数据

--学生表
Declare @Student Table
(
	StudentId Int,
	StudentName Nvarchar(20)
)
--课程表
Declare @Course Table
(
	CourseId Int,
	CourseName Nvarchar(20)
)
--学生课程配置表
Declare @StudentCourseConfig Table
(
	Config Int Identity(1,1),
	StudentId Int,
	CourseId Int
)

Insert Into @Student(StudentId,StudentName)
				Values(1001,'MK'),(1002,'小刘'),(1003,'徐总')

Insert Into @Course(CourseId,CourseName)
				Values(2001,'语文'),(2002,'数学'),(2003,'英语')
					,(2004,'历史'),(2005,'物理')

Insert Into @StudentCourseConfig(StudentId,CourseId)
				Values(1001,2001),(1001,2002),(1001,2003)
					,(1001,2004),(1001,2005)
					,(1002,2003),(1002,2004),(1002,2005)
					,(1003,2004),(1003,2003)

查询学生学习课程信息

SELECT T1.StudentId,T1.StudentName,
	Stuff((SELECT ',' + CourseName FROM @Course C1 
		LEFT JOIN @StudentCourseConfig C2 ON C1.CourseId = C2.CourseId 
		WHERE C2.StudentId = T1.StudentId
		FOR XML PATH('')),1,1,''
	) CourseName
FROM @Student T1

查询同时学习 数学、历史的学生

SELECT StudentId,StudentName FROM (
	--先查询出学生对于的学习课程
	SELECT T1.StudentId,T1.StudentName,
		','+Stuff((SELECT Cast(C1.CourseId AS Nvarchar(10)) + ',' 
			FROM @StudentCourseConfig C1 
			WHERE C1.StudentId = T1.StudentId
			FOR XML PATH('')),1,0,''
		) CourseIds
	FROM @Student T1
)A
WHERE A.CourseIds LIKE '%,2002,%'
	AND A.CourseIds LIKE '%,2004,%'

ps:也可以使用Partition By来实现，参考：/article/details/160

Stuff参数解析：

    stuff(param1, startIndex, length, param2)

    将param1中自startIndex(SQL中都是从1开始，而非0)起，删除length个字符，然后用param2替换删掉的字符。

    param1：一个字符数据表达式。param1可以是常量、变量，也可以是字符列或二进制数据列。

    startIndex：一个整数值，指定删除和插入的开始位置。如果 startIndex或 length 为负，则返回空字符串。

                        如果startIndex比param1长，则返回空字符串。startIndex可以是 bigint 类型。

    length：一个整数，指定要删除的字符数。如果 length 比param1长，则最多删除到param1 中的最后一个字符。length 可以是 bigint 类型。

    param2：返回类型。如果param1是受支持的字符数据类型，则返回字符数据。如果param1是一个受支持的 binary 数据类型，则返回二进制数据。

示例 

以下示例在第一个字符串 abcdef 中删除从第 2 个位置（字符 b）开始的三个字符，然后在删除的起始位置插入第二个字符串，从而创建并返回一个字符串。

SELECT STUFF('abcdef', 2, 3, 'ijklmn'); 

返回结果

aijklmnef