SqlServer Stuff用法
2018-05-22 16:11
1878 人阅读
分类:
查询数据的时候经常需要将某列多行合并显示到一列中
这里模拟下数据
--学生表 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
和博主交个朋友吧