34,838
社区成员




use tempdb;
/*
create table menu
(
id int not null,
name nvarchar(10) not null
);
insert into menu(id,name)
values
(1,'干煸四季豆'),
(2,'回锅肉'),
(3,'青椒肉丝');
create table detail
(
id int not null,
composition nvarchar(10) not null
);
insert into detail(id,composition)
values
(1,'四季豆'),
(1,'青油'),
(1,'姜'),
(1,'蒜'),
(1,'花椒'),
(2,'猪肉'),
(2,'青椒'),
(2,'姜'),
(2,'蒜'),
(2,'葱'),
(3,'青椒'),
(3,'猪肉'),
(3,'姜');
*/
select id,name,
STUFF((select ',' + composition from detail as t2 where t2.id = t1.id for xml path('')),1,1,'') as composition
from menu as t1;
use tempdb;
/*
create table menu
(
id int not null,
name nvarchar(10) not null
);
insert into menu(id,name)
values
(1,'干煸四季豆'),
(2,'回锅肉'),
(3,'青椒肉丝');
create table detail
(
id int not null,
composition nvarchar(10) not null
);
insert into detail(id,composition)
values
(1,'四季豆'),
(1,'青油'),
(1,'姜'),
(1,'蒜'),
(1,'花椒'),
(2,'猪肉'),
(2,'青椒'),
(2,'姜'),
(2,'蒜'),
(2,'葱'),
(3,'青椒'),
(3,'猪肉'),
(3,'姜');
*/
select distinct t3.id,t3.name,
STUFF
(
(
select ',' + composition
from
(
select t4.id,t4.name,t5.composition
from menu as t4
join detail as t5 on t4.id = t5.id
) as t6
where t3.id = t6.id
for xml path('')
),1,1,''
) as composition
from
(
select t1.id,t1.name,t2.composition
from menu as t1
join detail as t2 on t1.id = t2.id
) as t3;
CREATE TABLE menu(id INT ,NAME VARCHAR(20))
INSERT INTO menu
SELECT 1, '干煸四季豆' UNION ALL
SELECT 2, '回锅肉 ' UNION ALL
SELECT 3, '青椒肉丝' ;
CREATE TABLE detail(id INT ,compositon VARCHAR(20))
INSERT INTO detail
SELECT 1, '四季豆' UNION ALL
SELECT 1, '青油' UNION ALL
SELECT 1, '姜' UNION ALL
SELECT 1, '蒜' UNION ALL
SELECT 1, '花椒' UNION ALL
SELECT 2, '猪肉 ' UNION ALL
SELECT 2, '青椒 ' UNION ALL
SELECT 2, '姜 ' UNION ALL
SELECT 2, '蒜 ' UNION ALL
SELECT 2, '葱 ' UNION ALL
SELECT 3, '青椒 ' UNION ALL
SELECT 3, '猪肉' UNION ALL
SELECT 3, '姜' ;
SELECT M.ID,M.NAME,
COL=STUFF((SELECT ','+ D.COMPOSITON FROM DETAIL D WHERE D.ID=M.ID FOR XML PATH('')),1,1,'')
FROM MENU M
GROUP BY M.ID,M.NAME
ID NAME COL
----------- -------------------- ----------------------------------------------------------
1 干煸四季豆 四季豆,青油,姜,蒜,花椒
2 回锅肉 猪肉 ,青椒 ,姜 ,蒜 ,葱
3 青椒肉丝 青椒 ,猪肉,姜
(3 row(s) affected)
if object_id('[menu]') is not null drop table [menu]
go
create table [menu]([id] int,[name] varchar(10))
insert [menu]
select 1,'干煸四季豆' union all
select 2,'回锅肉' union all
select 3,'青椒肉丝'
go
if object_id('[detail]') is not null drop table [detail]
go
create table [detail]([id] int,[composition] varchar(6))
insert [detail]
select 1,'四季豆' union all
select 1,'青油' union all
select 1,'姜' union all
select 1,'蒜' union all
select 1,'花椒' union all
select 2,'猪肉' union all
select 2,'青椒' union all
select 2,'姜' union all
select 2,'蒜' union all
select 2,'葱' union all
select 3,'青椒' union all
select 3,'猪肉' union all
select 3,'姜'
go
select a.id,a.name,
com1=stuff((select ','+composition from detail where id=a.id for xml path('')),1,1,'')
from menu a
group by a.id,a.name
/**
id name com1
----------- ---------- ------------------------------------------
1 干煸四季豆 四季豆,青油,姜,蒜,花椒
2 回锅肉 猪肉,青椒,姜,蒜,葱
3 青椒肉丝 青椒,猪肉,姜
(3 行受影响)
**/
select a.id,a.name,
com1=stuff((select ','+composition from detail where id=a.id for xml path('')),1,1,'')
from menu a
group by a.id,a.name