导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

用什么样的sql语句可以得到每道菜的具体构成?

yyouyou 2011-06-21 11:17:28
有两个表,一个是菜品名表: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 青椒肉丝 青椒,猪肉,姜

先谢谢大虾了!
...全文
151 点赞 收藏 11
写回复
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
CainLai 2011-06-22
各位大神 有2000的写法么?
回复
javatemptation 2011-06-22

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;
回复
xuexiaodong2009 2011-06-22
select *,stuff((select ' ' + t2.composition from menu t1,detail t2
where t2.ID=t1.ID
for xml path('')) , 1 , 1 , '') as com1
from menu

试试
回复
xuexiaodong2009 2011-06-22
/*

把满足 t2.NodeID=t1.NodeID的tableA 的字段NodeName 以逗号分隔开合并为一个字段输出

*/

select *,stuff((select ',' + t1.NodeName from tableA t1,tableB t2
where t2.NodeID=t1.NodeID
for xml path('')) , 1 , 1 , '') as text
from tableA
回复
javatemptation 2011-06-22

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;
回复
yyouyou 2011-06-22
谢谢楼上各位大虾了!
回复
zsh0809 2011-06-22
[Quote=引用 9 楼 cainlai 的回复:]

各位大神 有2000的写法么?
[/Quote]
2000不支持XML,可以用函数跟游标来处理
回复
zsh0809 2011-06-21
合并拆分的方法可以找找精华帖里面的,有利用游标的,也有利用函数来出来的,2005支持XML处理,如下:
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)
回复
FlySQL 2011-06-21
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 行受影响)
**/
回复
FlySQL 2011-06-21
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
回复
yyouyou 2011-06-21

<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>

回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告