求一条查询语句····

[wu5224311]

零件（part）表的字段是
ID partname
1 A
2 B
3 C
产品表（produce）的字段是
ID producename
1 E
2 F

关系表（relation） 的字段是
产品id 零件1id 数量1 零件2id 数量2
1 1 1 2 2
2 1 1 3 3


怎么用查询语句输出：
产品 零件
E A*1+B*2
F A*1+C*3

[百年树人]

if object_id('[part]') is not null drop table [part]
go
create table [part]([ID] int,[partname] varchar(1))
insert [part]
select 1,'A' union all
select 2,'B' union all
select 3,'C'
go
if object_id('[produce]') is not null drop table [produce]
go
create table [produce]([ID] int,[producename] varchar(1))
insert [produce]
select 1,'E' union all
select 2,'F'
go
if object_id('[relation]') is not null drop table [relation]
go
create table [relation]([产品id] int,[零件1id] int,[数量1] int,[零件2id] int,[数量2] int)
insert [relation]
select 1,1,1,2,2 union all
select 2,1,1,3,3
go

select b.producename as 产品,
  a.partname+'*'+ltrim(c.[数量1])+'+'+d.partname+'*'+ltrim(c.[数量2]) as 零件
from relation c
left join produce b on c.产品id=b.id
left join part a on a.id=c.[零件1id]
left join part d on d.id=c.[零件2id]

/**
产品   零件
---- -----------------------------
E    A*1+B*2
F    A*1+C*3

(2 行受影响)
**/

[牛哥_]

[Quote=引用 1 楼 的回复:]

SQL code
if object_id('[part]') is not null drop table [part]
go
create table [part]([ID] int,[partname] varchar(1))
insert [part]
select 1,'A' union all
select 2,'B' union all
select 3,'C'
go
if ob……
[/Quote]


+1

我想的多了 学习

[以学习为目的]

select b.producename as 产品,
  a.partname+'*'+ltrim(c.[数量1])+'+'+d.partname+'*'+ltrim(c.[数量2]) as 零件
from relation c
left join produce b on c.产品id=b.id
left join part a on a.id=c.[零件1id]
left join part d on d.id=c.[零件2id]

[walkman_22]

select 
pr.producename,
r1.partname + '*' + r1.数量1,
r2.partname + '*" + r2.数量2
from part p,produce pr,relation r1, relation r2
where r1.1id=p.1id
and r2.2id = p.2id
and r1.id = pr.id

感觉没有必要用left join~~~供楼主参考

[wu5224311]

谢谢树哥···
[Quote=引用 1 楼 的回复:]

SQL code
if object_id('[part]') is not null drop table [part]
go
create table [part]([ID] int,[partname] varchar(1))
insert [part]
select 1,'A' union all
select 2,'B' union all
select 3,'C'
go
if ob……
[/Quote]