有两个表,一个是菜品名表:menu(id,name),一个是菜品成分组成表:detail(id,compositon),其中menu数据如下
id name
1 干煸四季豆
2 回锅肉
3 青椒肉丝 detail数据如下:
id composition
1 四季豆
1 青油
1 姜
1 蒜
1 花椒
2 猪肉
2 青椒
2 姜
2 蒜
2 葱
3 青椒
3 猪肉
3 姜 请问用什么样的语句可以得到如下形式的内容id name com1 com2 com3 com4 com5
1 干煸四季豆 四季豆 青油 姜 蒜 花椒
2 回锅肉 猪肉 青椒 姜 蒜 葱
3 青椒肉丝 青椒 猪肉 姜 或者这样也行id name com1
1 干煸四季豆 四季豆,青油,姜,蒜,花椒
2 回锅肉 猪肉,青椒,姜,蒜,葱
3 青椒肉丝 青椒,猪肉,姜 先谢谢大虾了!
id name
1 干煸四季豆
2 回锅肉
3 青椒肉丝 detail数据如下:
id composition
1 四季豆
1 青油
1 姜
1 蒜
1 花椒
2 猪肉
2 青椒
2 姜
2 蒜
2 葱
3 青椒
3 猪肉
3 姜 请问用什么样的语句可以得到如下形式的内容id name com1 com2 com3 com4 com5
1 干煸四季豆 四季豆 青油 姜 蒜 花椒
2 回锅肉 猪肉 青椒 姜 蒜 葱
3 青椒肉丝 青椒 猪肉 姜 或者这样也行id name com1
1 干煸四季豆 四季豆,青油,姜,蒜,花椒
2 回锅肉 猪肉,青椒,姜,蒜,葱
3 青椒肉丝 青椒,猪肉,姜 先谢谢大虾了!
<p class="MsoNormal"><span style="font-family: 宋体">有两个表,一个是菜品名表:</span><span lang="EN-US">menu(id,name)</span><span style="font-family: 宋体">,一个是菜品成分组成表:</span><span lang="EN-US">detail(id,compositon)</span><span style="font-family: 宋体">,其中</span><span lang="EN-US">menu</span><span style="font-family: 宋体">数据如下</span></p>
<p class="MsoNormal"><span lang="EN-US">id name</span></p>
<p class="MsoNormal"><span lang="EN-US">1 </span>
<span style="font-family: 宋体">干煸四季豆</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">2 </span>
<span style="font-family: 宋体">回锅肉</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">3 </span>
<span style="font-family: 宋体">青椒肉丝</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">detail</span><span style="font-family: 宋体">数据如下:</span></p>
<p class="MsoNormal"><span lang="EN-US">id composition</span></p>
<p class="MsoNormal"><span lang="EN-US">1 </span>
<span style="font-family: 宋体">四季豆</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">1 </span>
<span style="font-family: 宋体">青油</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">1 </span>
<span style="font-family: 宋体">姜</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">1 </span>
<span style="font-family: 宋体">蒜</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">1 </span>
<span style="font-family: 宋体">花椒</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">2 </span>
<span style="font-family: 宋体">猪肉</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">2 </span>
<span style="font-family: 宋体">青椒</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">2 </span>
<span style="font-family: 宋体">姜</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">2 </span>
<span style="font-family: 宋体">蒜</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">2 </span>
<span style="font-family: 宋体">葱</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">3 </span>
<span style="font-family: 宋体">青椒</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">3 </span>
<span style="font-family: 宋体">猪肉</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">3 </span>
<span style="font-family: 宋体">姜</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span style="font-family: 宋体">请问用什么样的语句可以得到如下形式的内容</span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">id name
com1 com2 com3 com4
com5 </span></p>
<p class="MsoNormal"><span lang="EN-US">1 </span>
<span style="font-family: 宋体">干煸四季豆</span><span lang="EN-US"> </span>
<span style="font-family: 宋体">四季豆</span><span lang="EN-US"> </span><span style="font-family: 宋体">青油</span><span lang="EN-US">
</span><span style="font-family: 宋体">姜</span><span lang="EN-US"> </span>
<span style="font-family: 宋体"> 蒜</span><span lang="EN-US">
</span><span style="font-family: 宋体">花椒</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">2 </span>
<span style="font-family: 宋体">回锅肉</span><span lang="EN-US"> </span>
<span style="font-family: 宋体"> 猪肉</span><span lang="EN-US"> </span>
<span style="font-family: 宋体"> 青椒</span><span lang="EN-US"> </span>
<span style="font-family: 宋体">姜</span><span lang="EN-US"> </span>
<span style="font-family: 宋体"> 蒜</span><span lang="EN-US">
</span><span style="font-family: 宋体">葱</span><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">3 </span>
<span style="font-family: 宋体">青椒肉丝</span><span lang="EN-US"> </span>
<span style="font-family: 宋体">青椒</span><span lang="EN-US"> </span><span style="font-family: 宋体">
猪肉</span><span lang="EN-US"> </span><span style="font-family: 宋体">姜</span><span lang="EN-US"> </span>
</p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span style="font-family: 宋体">或者这样也行</span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span lang="EN-US">id name
com1 </span></p>
<p class="MsoNormal"><span lang="EN-US">1 </span>
<span style="font-family: 宋体">干煸四季豆</span><span lang="EN-US"> </span>
<span style="font-family: 宋体">四季豆</span><span lang="EN-US">,</span><span style="font-family: 宋体">青油</span><span lang="EN-US">,</span><span style="font-family: 宋体">姜</span><span lang="EN-US">,</span><span style="font-family: 宋体">蒜</span><span lang="EN-US">,</span><span style="font-family: 宋体">花椒</span><span lang="EN-US">
</span></p>
<p class="MsoNormal"><span lang="EN-US">2 </span>
<span style="font-family: 宋体">回锅肉</span><span lang="EN-US"> </span>
<span style="font-family: 宋体"> 猪肉</span><span lang="EN-US">,</span><span style="font-family: 宋体">青椒</span><span lang="EN-US">,</span><span style="font-family: 宋体">姜</span><span lang="EN-US">,</span><span style="font-family: 宋体">蒜</span><span lang="EN-US">,</span><span style="font-family: 宋体">葱</span><span lang="EN-US">
</span></p>
<p class="MsoNormal"><span lang="EN-US">3 </span>
<span style="font-family: 宋体">青椒肉丝</span><span lang="EN-US"> </span>
<span style="font-family: 宋体">青椒</span><span lang="EN-US">,</span><span style="font-family: 宋体">猪肉</span><span lang="EN-US">,</span><span style="font-family: 宋体">姜</span><span lang="EN-US">
</span></p>
<p class="MsoNormal"><span lang="EN-US"> </span></p>
<p class="MsoNormal"><span style="font-family: 宋体">先谢谢大虾了!</span></p>
com1=stuff((select ','+composition from detail where id=a.id for xml path('')),1,1,'')
from menu a
group by a.id,a.name
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,'姜'
goselect 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 行受影响)
**/
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.NAMEID NAME COL
----------- -------------------- ----------------------------------------------------------
1 干煸四季豆 四季豆,青油,姜,蒜,花椒
2 回锅肉 猪肉 ,青椒 ,姜 ,蒜 ,葱
3 青椒肉丝 青椒 ,猪肉,姜(3 row(s) affected)
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;
where t2.NodeID=t1.NodeID
for xml path('')) , 1 , 1 , '') as text
from tableA
where t2.ID=t1.ID
for xml path('')) , 1 , 1 , '') as com1
from menu试试
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;