mysql xml path_使用 SQL的 for xml path来进行字符串拼接

本篇主要讲怎么利用SQL的FOR XML PATH 参数来进行字符串拼接，FOR XML PATH的用法很简单，它会以xml文件的形式来返回数据。

我的讲解步骤：

1：构造初始数据

2：提出问题

3：简单介绍FOR XML PATH

4：解答问题

1.构造初始数据

举出一个经典的学生课程例子，共有学生、课程与学生课程三张表。

表1:Student

student_id

student_name

1

张三

2

李四

3

王五

表2:Course

course_id

course_name

1

语言

2

数学

3

英语

表3:Student_Course

student_id

course_id

1

2

1

3

2

1

2

3

3

3

脚本：

createtablestudent

(

student_idintprimarykey,

student_namenvarchar(50)notnull)createtablecourse

(

course_idintprimarykey,

course_namenvarchar(50)notnull)createtablestudent_course

(

student_idintnotnull,

course_idintnotnull,primarykey(student_id,course_id)

)

2.提出问题

写一条SQL语句，查询显示出下列结果：

student_name

course_name

张三

数学，英语

李四

语言，英语

王五

英语

3.简单介绍FOR XML PATH

FORXMLPATH语句能够把查询的数据生成XML数据，举个例子，针对student表，以前SQL语句的查询结果为：

selectstr(student_id)+','+student_namefromstudentforxml path('student')

查询结果：

1,张三2,李四3,王五

student已成为一个xml文件中的结点了，再看看FOR XML PATH('')的效果，针对上述SQL作出修改，

selectstr(student_id)+','+student_namefromstudentforxml path('')

查询结果：

1,张三2,李四3,王五

看得出来，这个参数自动把我们的查询结果串接在一起了，这下子，要做字符串拼接就很简单了！

4. 解答问题

要查询想要的结果，我们首先用一般的SQL语句，连接三个表之后的结果为：

selecta.student_name,b.course_namefromstudent_course c,student a,course bwherec.student_id=a.student_idandc.course_id=b.course_id

查询结果：

student_name

course_name

张三

数学

张三

英语

李四

语文

李四

英语

王五

英语

我们把这个查询结果看作为一个临时表，与自身进行一次连接，再得用FOR XML PATH('')参数来对课程course_name列进行拼接，再得用子查询功能。这样就得到一个每一个学生的所选的所有课程，由于上表会存在同一学生的多条记录，所以需要对最后的结果按学生进行分组，先看看查询语句：

selectstudent_name,

(selectcourse_name+','from(selectstudent_name,course_namefrom(selecta.student_name,b.course_namefromstud_course c,student a,course bwherec.student_id=a.student_idandc.course_id=b.course_id

)asa

)asbwherec.student_name=b.student_nameforxml path('')

)ascourse_namefrom(selecta.student_name,b.course_namefromstudent_course c,student a,course bwherec.student_id=a.student_idandc.course_id=b.course_id

)asc  group by student_name

查询结果：

student_name

course_name

张三

数学，英语,

李四

语言，英语,

王五

英语,

还有个小问题， course_name后面多出一个,号，最后做一次裁剪，假设上面的SQL语句作为一个子查询 subquery

selectstudent_name,left(course_name,len(course_name)-1)from(........)assubquery

这样，就可以得出最终的结果！可以看得出来FOR XML PATH('') 参数非常强大！

PS:有很多人把这个叫行转列，我个人并不这么认为，虽然这和行转列有点像，但是这更像是字符串拼接！就把它这么叫好了!

PS:我的测试环境：SQL server 2008