日期:2014-05-17 浏览次数:20597 次
有两个表:订单表、订单明细表
用SQL语句查询得出以下格式,不能用自定义函数:
---------------------------------------------------
订单ID | 订单编号 | 商品
---------------------------------------------------
1 | 20121110 | 商品1,商品2,商品3……
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
-- 查询处理
select * from(select distinct id from tb)a outer apply(
select [values]= stuff(replace(replace(
(
select value from tb n
where id = a.id
for xml auto
), ' <N value="', ','), '"/>', ''), 1, 1, '')
)N
drop table tb
/*
id values
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
(2 行受影响)
*/
--SQL2005中的方法
create table tb(id int, value varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go
select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '')
from tb
group by id
/*
id values
----------- --------------------
1 aa,bb
2 aaa,bbb,ccc
(2 row(s) affected)
*/
drop table tb