大牛们求帮助

[xdrs1314]

如下：
主表User 有 id name 列

外表Order 有 id userid（引用USER表id） goods_name 列

如何查询出一条记录里面包含 name和它对应的多个goods_name 如下:张三的多个商品

name goods_name1 goods_name2 goods_name3 .......
张三 笔记本电脑 华为手机 宝马汽车 .......

谢谢！

[ACMAIN_CHM]

select u.name, substr( xmlserialize( xmlagg( xmltext( concat( ', ', goods_name ) ) ) as varchar( 1024 ) ), 3 ) from user u inner join order o on u.id=o.userid group by u.name

[xdrs1314]

引用 1 楼 ACMAIN_CHM 的回复:

select u.name, substr( xmlserialize( xmlagg( xmltext( concat( ', ', goods_name ) ) ) as varchar( 1024 ) ), 3 ) from user u inner join order o on u.id=o.userid group by u.name ……

大神 好像不行啊, 语法报错.我的是dB2数据库，求正确语法

[WWWWA]

你的记录 是什么，要求结果是什么，贴出来

[wwwwb]

贴建表及插入记录的SQL，及要求结果出来看看

[xdrs1314]

create table Users (
uid  int  primary key  not null,
uname  varchar (30)
);
create table OrderInfo(
oid int primary key not null,
uid  int ,
gid int 
);
create table goods (
 gid  int primary key not null,
 goods_name  varchar (200)
);

insert into Users  values(1, '张三');
insert into Users  values(2, '李四');
insert into Users  values(3, '王五');
insert into Users  values(4, '赵六');
insert into Users  values(5, '马七');



insert into goods  values(1, '手机');
insert into goods  values(2, '电脑');
insert into goods  values(3, '汽车');
insert into goods  values(4, '别墅');
insert into goods  values(5, '相机');

insert into orderinfo  values(1,1,1);
insert into orderinfo  values(2,1,2);
insert into orderinfo  values(3,1,3);
insert into orderinfo  values(4,2,3);
insert into orderinfo  values(5,2,1);
insert into orderinfo  values(6,2,4);
insert into orderinfo  values(7,3,1);
insert into orderinfo  values(8,2,5);
insert into orderinfo  values(9,3,4);

需要结果如下： 姓名 手机 电脑 汽车 别墅 相机 张三 是 是 是 否 否 李四 是 否 是 是 是 王五 是 否 否 是 否 赵六 否 否 否 否 否 马七 否 否 否 否 否 谢谢！

[mysdzlt2007]

+1，再麻烦一点方法就是存储过程吧。。

引用 1 楼 ACMAIN_CHM 的回复:

select u.name, substr( xmlserialize( xmlagg( xmltext( concat( ', ', goods_name ) ) ) as varchar( 1024 ) ), 3 ) from user u inner join order o on u.id=o.userid group by u.name

[WWWWA]

SELECT A.`uname`, MAX(CASE WHEN C.`goods_name`= '手机' THEN '是' ELSE '否' END ) AS 手机, MAX(CASE WHEN C.`goods_name`='电脑' THEN '是' ELSE '否' END) AS 电脑, MAX(CASE WHEN C.`goods_name`='汽车' THEN '是' ELSE '否' END) AS 汽车, MAX(CASE WHEN C.`goods_name`='别墅' THEN '是' ELSE '否' END) AS 别墅, MAX(CASE WHEN C.`goods_name`= '相机' THEN '是' ELSE '否' END) AS 相机 FROM Users A LEFT JOIN orderinfo B ON A.uid=B.uid LEFT JOIN goods C ON B.`gid`=C.`gid` GROUP BY A.`uname` 如果物品不固定，用字符串累加生成SQL语句，再执行的方法

[ChangeMyself2012]

SQL server中的语法 希望能为楼主提供点思路

create table Users (
uid  int  primary key  not null,
uname  varchar (30)
);
create table OrderInfo(
oid int primary key not null,
uid  int ,
gid int 
);
create table goods (
 gid  int primary key not null,
 goods_name  varchar (200)
);
 
insert into Users  values(1, '张三');
insert into Users  values(2, '李四');
insert into Users  values(3, '王五');
insert into Users  values(4, '赵六');
insert into Users  values(5, '马七');
 
 
 
insert into goods  values(1, '手机');
insert into goods  values(2, '电脑');
insert into goods  values(3, '汽车');
insert into goods  values(4, '别墅');
insert into goods  values(5, '相机');
 
insert into orderinfo  values(1,1,1);
insert into orderinfo  values(2,1,2);
insert into orderinfo  values(3,1,3);
insert into orderinfo  values(4,2,3);
insert into orderinfo  values(5,2,1);
insert into orderinfo  values(6,2,4);
insert into orderinfo  values(7,3,1);
insert into orderinfo  values(8,2,5);
insert into orderinfo  values(9,3,4);

select * from Users
select * from goods
select * from orderinfo


--创建临时表
if OBJECT_ID('cb') is not null 
drop table cb
if OBJECT_ID('usergoods') is not null 

--创建视图
drop view usergoods
go
create view usergoods
as
select u.uname as 用户,g.gid,g.goods_name from orderinfo as i
left join Users as u on i.uid=u.uid
left join goods as g on g.gid=i.gid

go
--得到商品名
Declare @goods nvarchar(max),
@sql nvarchar(max)

select @goods=isnull(@goods+',','')+goods_name from goods
--拼接 将行转列数据导入到临时表的sql语句
set @sql='select * into cb from(
select * from usergoods
pivot 
(
max(gid) for goods_name in('+@goods+')
)t)b'
exec (@sql)
select * from cb 

/*
用户 手机 电脑 汽车 别墅 相机
李四	1	NULL	3	4	5
王五	1	NULL	NULL	4	NULL
张三	1	2	3	NULL	NULL
*/