for xml path:
语法:
select 字段名 form table for xml path('')
讲解:
for xml path 语句能够把查询的数据生成xml数据
查询结果:
stuff:
语法:
stuff('字符串',start,length,'替换对象')
eg:select stuff('abcdef',1,2,'gh')
讲解:
删除指定长度的字符,并在指定的起点插入另一组字符
结果:
aghdef
结合使用
sql:
select s.sname,
course = (select (stuff (( select ',' +(select cname from course c where c.cid = sc1.cid )
from sc sc1 where sc1.sid = s1.sid for xml path('')),1,1,''))
from student s1 where s1.sId = s.sId )
from student s
查询结果:
分解:
sql:
select ',' +(select cname from course c where c.cid = sc1.cid )
from sc sc1
--where sc1.sid = s1.sid
for xml path('')
查询结果:
最后使用stuff函数对字符串删除第一个逗号。