SQL字符串拼接与拆分使⽤ SQL的 for xml path来进⾏字符串拼接
本篇主要讲怎么利⽤SQL的FORXMLPATH 参数来进⾏字符串拼接，FORXMLPATH的⽤法很简单，它会以xml⽂件的形式来返回数据。 ⼩⼩⽰例⼀：
Create table test (id int，变量 varchar(10))
go
insert test values(1,'ac')
insert test values(1,'bs')
insert test values(1,'cg')
insert test values(1,'as')
insert test values(2,'ds')
insert test values(2,'dh')
go
select ID,LEFT(变量列表,LEN(变量列表)-1) as a
from
(
select ID,(select 变量+'|' from test as B where B.id = A.id for XML path('')) as 变量列表
from test as A
group by ID
) as C
我的讲解步骤：
1：构造初始数据2：提出问题3：简单介绍FORXMLPATH
4：解答问题
1.构造初始数据
举出⼀个经典的学⽣课程例⼦，共有学⽣、课程与学⽣课程三张表。
表1:Student
student_idstudent_name1
张三
2
李四
3王五
表2:Course
course_idcourse_name1
语⾔
2
数学
3英语
表3:Student_Course
student_idcourse_id
1
2
1
3
21
23
3
3
脚本：
createtable student
(
student_id intprimarykey,
student_name nvarchar(50) notnull
)
createtable course
(
course_id intprimarykey,
course_name nvarchar(50) notnull
)
createtable student_course
(
student_id intnotnull,
course_id intnotnull,
primarykey(student_id,course_id)
)
2.提出问题
写⼀条SQL语句，查询显⽰出下列结果：
student_namecourse_name
张三
数学，英语
李四
语⾔，英语
王五
英语
3.简单介绍 FORXMLPATH
FORXMLPATH 语句能够把查询的数据⽣成XML数据，举个例⼦，针对student表，以前SQL语句的查询结果为：
selectstr(student_id) +','+ student_name from student for xml path('student')
查询结果：
<student> 1，张三</student>
<student> 2，李四</student>
<student> 3，王五</student>
student已成为⼀个xml⽂件中的结点了，再看看FORXMLPATH('')的效果，针对上述SQL作出修改，
selectstr(student_id) +','+ student_name from student for xml path('')
查询结果：
1，张三 2，李四 3，王五
看得出来，这个参数⾃动把我们的查询结果串接在⼀起了，这下⼦，要做字符串拼接就很简单了！
4. 解答问题要查询想要的结果，我们⾸先⽤⼀般的SQL语句，连接三个表之后的结果为：
select a.student_urse_name from student_course c,student a,course b where
c.student_id=a.student_id urse_urse_id
查询结果：
student_namecourse_name
张三
数学
张三
英语
李四
语⽂
李四
英语
王五
英语
我们把这个查询结果看作为⼀个临时表，与⾃⾝进⾏⼀次连接，再得⽤FORXMLPATH('')参数来对课程course_name列进⾏拼接，再得⽤⼦查询功能。这样就得到⼀个每⼀个学⽣的所选的所有课程，由于上表会存在同⼀学⽣的多条记录，所以需要对select student_name,
(select course_name+','from
(
select student_name,course_name from
(
select a.student_urse_name from stud_course c,student a,course b where c.student_id=a.student_id urse_urse_id
) as a
) as b where c.student_name=b.student_name for xml path('')
) as course_name
from
(
select a.student_urse_name from student_course c,student a,course b where c.student_id=a.student_id urse_urse_id
) as c group by student_name
查询结果：
student_namecourse_name
张三
数学，英语,
李四
语⾔，英语,
王五
英语,
还有个⼩问题， course_name后⾯多出⼀个，号，最后做⼀次裁剪，假设上⾯的SQL语句作为⼀个⼦查询 subquery
select student_name,left(course_name,len(course_name)-1) from (........) as subquery
这样，就可以得出最终的结果！可以看得出来FORXMLPATH('') 参数⾮常强⼤！
PS：有很多⼈把这个叫⾏转列，我个⼈并不这么认为，虽然这和⾏转列有点像，但是这更像是字符串拼接！就把它这么叫好了!
PS：我的测试环境：SQL server 2008
Author：repository
From： repositoryblogs
本⽂版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在⽂章页⾯明显位置给出原⽂连接，否则保留追究法律责任的权利。 SQL 拆分字符串事例数据库1
USE tempdb;
GO
IF OBJECT_ID('dbo.Arrays') IS NOT NULL
DROP TABLE dbo.Arrays;
GO
CREATE TABLE dbo.Arrays
(
arrid VARCHAR(10) NOT NULL PRIMARY KEY,
array VARCHAR(8000) NOT NULL
)
INSERT INTO Arrays(arrid, array) VALUES('A', '20,22,25,25,14');
INSERT INTO Arrays(arrid, array) VALUES('B', '30,33,28');
INSERT INTO Arrays(arrid, array) VALUES('C', '12,10,8,12,12,13,12,14,10,9');
INSERT INTO Arrays(arrid, array) VALUES('D', '-4,-6,-4,-2');
事例数据库2
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;sql server拼接字符串函数
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO
第⼀部思路 :
SELECT arrid, array, n
FROM dbo.Arrays
JOIN dbo.Nums
ON n <= LEN(array)
AND SUBSTRING(array, n, 1) = ',';
第⼆部思路 :
SELECT arrid, array, n
FROM dbo.Arrays
JOIN dbo.Nums
ON n <= LEN(array)
AND SUBSTRING(',' + array, n, 1) = ',';
第三部思路 :
SELECT arrid,
SUBSTRING(array, n, CHARINDEX(',', array + ',', n) - n) AS element
FROM dbo.Arrays
JOIN dbo.Nums
ON n <= LEN(array)
AND SUBSTRING(',' + array, n, 1) = ',';
第四部思路 :
SELECT arrid,
n - LEN(REPLACE(LEFT(array, n), ',', '')) + 1 AS pos,
CAST(SUBSTRING(array, n, CHARINDEX(',', array + ',', n) - n)
AS INT) AS element
FROM dbo.Arrays
JOIN dbo.Nums
ON n <= LEN(array)
AND SUBSTRING(',' + array, n, 1) = ',';
SQL 2005 以上版本可使⽤，不使⽤Number辅助表
WITH SplitCTE AS
(
SELECT arrid, 1 AS pos, 1 AS startpos,
CHARINDEX(',', array + ',') - 1 AS endpos
FROM dbo.Arrays
WHERE LEN(array) > 0
UNION ALL
SELECT Prv.arrid, Prv.pos + 1, dpos + 2,
CHARINDEX(',', Cur.array + ',', dpos + 2) - 1
FROM SplitCTE AS Prv
JOIN dbo.Arrays AS Cur
ON Cur.arrid = Prv.arrid
AND CHARINDEX(',', Cur.array + ',', dpos + 2) > 0
)
SELECT A.arrid, pos,
CAST(SUBSTRING(array, startpos, endpos-startpos+1) AS INT) AS element FROM dbo.Arrays AS A
JOIN SplitCTE AS S
ON S.arrid = A.arrid
ORDER BY arrid, pos;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论