首页 > 代码库 > for mxl path

for mxl path

 

 

废话不多说,直接上例子 简单明了

create  table tb_class
(
    classId int ,
    className varchar(10)
)
go
insert into tb_class values(1,一班)
insert into tb_class values(2,二班)
insert into tb_class values(3,三班)
go
create table tb_student
(
    classId int,
    stuId varchar(5),
    stuName varchar(10)
)
go
insert into tb_student values(1,1001,张三)
insert into tb_student values(1,1002,李四)
insert into tb_student values(1,1003,王二)
insert into tb_student values(1,1004,麻子)
insert into tb_student values(2,2001,王尼玛)
insert into tb_student values(2,2002,唐马儒)
insert into tb_student values(2,2003,张全蛋)
go
select * from tb_class
select * from tb_student

1.

select stuname from tb_student for xml path

 效果

<row>
  <stuname>张三</stuname>
</row>
<row>
  <stuname>李四</stuname>
</row>
<row>
  <stuname>王二</stuname>
</row>
<row>
  <stuname>麻子</stuname>
</row>
<row>
  <stuname>王尼玛</stuname>
</row>
<row>
  <stuname>唐马儒</stuname>
</row>
<row>
  <stuname>张全蛋</stuname>
</row>

2

select stuname from tb_student for xml path(‘‘)

效果

<stuname>张三</stuname>
<stuname>李四</stuname>
<stuname>王二</stuname>
<stuname>麻子</stuname>
<stuname>王尼玛</stuname>
<stuname>唐马儒</stuname>
<stuname>张全蛋</stuname>

3  

select stuname+, from tb_student for xml path(‘‘)

效果
张三,李四,王二,麻子,王尼玛,唐马儒,张全蛋,

4

select LEFT(stuName, LEN(stuName)-1) from (
select (
select stuname+,from tb_student for xml path(‘‘)) as stuName ) a

张三,李四,王二,麻子,王尼玛,唐马儒,张全蛋

5

select 可以拼接字符串,+LEFT(stuName, LEN(stuName)-1) from (
select (
select stuname+, from tb_student for xml path(‘‘)) as stuName ) a

效果:可以拼接字符串,张三,李四,王二,麻子,王尼玛,唐马儒,张全蛋

for mxl path