博主喝口茶,一毛也是爱

收缩

SqlServer Stuff用法

147 人阅读
分类:

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

这里模拟下数据

--学生表
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,0,''
	) CourseName
FROM @Student T1

333.png

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

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,%'

444.png

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


和博主交个朋友吧
    发布篇幅
    • 文章总数:0
    • 原创:0
    • 转载:0
    • 译文:0
    文章分类
      文章存档
      阅读排行