有三张表
产品表(produce)主键是id
id producename
1 E
零件表(part) 主键是id
id partname
1 A
2 B产品关系表(relation) 外键 produceid 对应的是 produce表的id
produceid producename partname1 number1 partname2 number2
1 E A 1 B 2这是说 一个产品E需要1个A和两个B才能生产现在需要添加产品H 一个H需要 一个零件C和两个零件D才能生产··插入零件C的时候还需要判断 零件表中是不是存在C··如果存在就不插入,如果不存在,就把数据插入part表怎么用一条SQL语句就写出来···求大神解答··
产品表(produce)主键是id
id producename
1 E
零件表(part) 主键是id
id partname
1 A
2 B产品关系表(relation) 外键 produceid 对应的是 produce表的id
produceid producename partname1 number1 partname2 number2
1 E A 1 B 2这是说 一个产品E需要1个A和两个B才能生产现在需要添加产品H 一个H需要 一个零件C和两个零件D才能生产··插入零件C的时候还需要判断 零件表中是不是存在C··如果存在就不插入,如果不存在,就把数据插入part表怎么用一条SQL语句就写出来···求大神解答··
begin
insert into part
select max(id)+1 as ID,'C' from part
end
if not exists (select 1 from part where partname ='D')
begin
insert into part
select max(id)+1 as ID,'D' from part
end
insert into relation
select id ,producename,'C',1,'D',2
from produce where producename = 'H'
if not exists (select 1 from part where partname ='C')
begin
insert into part
select max(id)+1 as ID,'C' from part
end
if not exists (select 1 from part where partname ='D')
begin
insert into part
select max(id)+1 as ID,'D' from part
end--这一部分你插入成功了,下面你分开执行这一句
insert into relation
select id ,producename,'C',1,'D',2
from produce where producename = 'H'--这一部分执行有什么提示?
begin
insert into part
select max(id)+1 as ID,'C' from part
end
if not exists (select 1 from part where partname ='D')
begin
insert into part
select max(id)+1 as ID,'D' from part
end
if not exists (select 1 from produce where producename ='H')
begin
insert into produce
select max(id)+1 as ID,'H' from produce
endinsert into relation
select id ,producename,'C',1,'D',2
from produce where producename = 'H'