34,590
社区成员
发帖
与我相关
我的任务
分享
create table a
(prac1 varchar(10),型号 varchar(10),flag int)
insert into a
select 'W-1','AAA',3 union all
select 'W-2','BBB',3 union all
select 'W-3','CCC',3 union all
select 'W-4','DDD',3 union all
select 'T-5','EEE',4 union all
select 'T-6','FFF',4 union all
select 'T-7','GGG',4 union all
select 'C1','QC',5 union all
select 'C2','EV',5 union all
select 'C3','QX',5 union all
select 'C4','BBE',5
go
create table b
(组名 varchar(10),型号 varchar(10),属性 varchar(10))
insert into b
select '型号A','W-1','门套' union all
select '型号A','W-2','门套' union all
select '型号A','W-3','门套' union all
select '型号B','EEE','木门' union all
select '型号B','FFF','木门' union all
select '型号C','QC','装饰材料' union all
select '型号C','EV','装饰材料' union all
select '型号C','QX','装饰材料'
go
select a.prac1,a.型号,a.flag,b.属性,b.组名
into #tb
from a left join b on b.型号 = (case when a.flag = 3 then a.prac1 else a.型号 end)
update t
set t.属性 = (select max(属性) from #tb where flag = t.flag),
t.组名 = (select max(组名) from #tb where flag = t.flag)
from #tb t
select 组名,型号,属性
into c
from #tb e
where not exists (select 1 from b where 组名 = e.组名
and 型号 = (case when e.flag = 3 then e.prac1 else e.型号 end))
select *
from c
drop table a,b,c,#tb
/***********
组名 型号 属性
---------- ---------- ----------
型号A DDD 门套
型号B GGG 木门
型号C BBE 装饰材料
(3 行受影响)
insert into c表
select a.组名,a.型号,b.属性
from B表 a,(select distinct 组名,属性 from B表) b
where a.组名=b.组名
and not exists(select 1 from A表 where prac1=a.型号)
select a.prac1,a.型号,a.flag,b.属性,b.组名
into #tb
from a left join b on a.型号 = b.型号
update t
set t.属性 = (select max(属性) from #tb where flag = t.flag),
t.组名 = (select max(组名) from #tb where flag = t.flag)
from #tb t
insert into c
select 组名,型号,属性
from #tb e
where not exists (select 1 from b where 组名 = e.组名 and 型号 = e.型号)
drop table #tb
declare @t1 table(prac1 varchar(10), 型号 varchar(10), flag int)
declare @t2 table(组名 varchar(10), 型号 varchar(10), 属性 varchar(10))
insert into @t1(prac1, 型号, flag)
select 'W-1','AAA',3 union all
select 'W-2','BBB',3 union all
select 'W-3','CCC',3 union all
select 'W-4','DDD',3 union all
select 'T-5','EEE',4 union all
select 'T-6','FFF',4 union all
select 'T-7','GGG',4 union all
select 'C1','QC',5 union all
select 'C2','EV',5 union all
select 'C3','QX',5 union all
select 'C4','BBE',5
insert into @t2(组名, 型号, 属性)
select '型号A','W-1','门套' union all
select '型号A','W-2','门套' union all
select '型号A','W-3','门套' union all
select '型号B','EEE','木门' union all
select '型号B','FFF','木门' union all
select '型号C','QC','装饰材料' union all
select '型号C','EV','装饰材料' union all
select '型号C','QX','装饰材料'
select '型号' + case when left(prac1, 1) = 'W' then 'A'
when left(prac1, 1) = 'T' then 'B'
when left(prac1, 1) = 'C' then 'C' end 组名,
case when left(prac1, 1) = 'W' then a.prac1 else a.型号 end 型号,
case when left(prac1, 1) = 'W' then '门套'
when left(prac1, 1) = 'T' then '木门'
when left(prac1, 1) = 'C' then '装饰材料' end 属性
from @T1 a left join @T2 b on case when left(prac1, 1) = 'W' then a.prac1 else a.型号 end = b.型号
where b.型号 is null