小弟是新人,有以下问题请教各位.有两个表结构,数据如下:
T1
Article 車花 印膠
QM708E-070705
QM708X-070705
QM708E-080101
T2
Article Addon Value
QM708 印膠 2
QM708 車花 1
QM708E-070705 車花 1
QM708E-070705 印膠
QM708X 印膠
QM708X 車花 查询逻辑:1.T1的Article前十三位在T2中是否有相应的Article,如果存在则带相应Addon的Value到T1中.
2.如果没有十三位相同的,则找前六位相同的Article是否存在.
3.如果再没有,则找前五位相同的.
以上例子得出结果应该是:
T1
Article 車花 印膠
QM708E-070705 1
QM708X-070705
QM708E-080101 1 2
T1
Article 車花 印膠
QM708E-070705
QM708X-070705
QM708E-080101
T2
Article Addon Value
QM708 印膠 2
QM708 車花 1
QM708E-070705 車花 1
QM708E-070705 印膠
QM708X 印膠
QM708X 車花 查询逻辑:1.T1的Article前十三位在T2中是否有相应的Article,如果存在则带相应Addon的Value到T1中.
2.如果没有十三位相同的,则找前六位相同的Article是否存在.
3.如果再没有,则找前五位相同的.
以上例子得出结果应该是:
T1
Article 車花 印膠
QM708E-070705 1
QM708X-070705
QM708E-080101 1 2
select t1.Article,t2.Addon,t2.value
from t1 join t2
on (left(t1.Article,13) = left(t2.Article,13)) or
(left(t1.Article, 6) = left(t2.Article, 6)) or
(left(t1.Article, 5) = left(t2.Article, 5)))
union all
select t1.Article,t1.車花,t2.印膠 from t1 inner join t2 on PATINDEX(left(t1.Article,6), T2.Article)>0
and PATINDEX(left(t1.Article,13), T2.Article)=0
union all
select t1.Article,t1.車花,t2.印膠 from t1 inner join t2 on PATINDEX(left(t1.Article,5), T2.Article)>0)
and PATINDEX(left(t1.Article,13), T2.Article)=0 and PATINDEX(left(t1.Article,6), T2.Article)=0
Create table ART1
(
Article varchar(30),
車花 int,
印膠 int
)
insert into ART1
select N'QM708E-070705',0,0 union all
select N'QM708X-070705' ,0,0union all
select N'QM708E-080101',0,0create table ART2
(
Article varchar(30),
Addon varchar(30),
[Value] int
)
insert into ART2
select N'QM708',N'印膠', 2 union all
select N'QM708',N'車花', 1 union all
select N'QM708E-070705',N'車花',1 union all
select N'QM708E-070705',N'印膠', 0union all
select N'QM708X',N'印膠', 0 union all
select N'QM708X',N'車花', 0
select A1.Article,
case Addon when '印膠' then A2.[Value]
when '車花' then A2.[Value] else 0 end from ART2 A2,ART1 A1 where A2.article=left(A1.article,13)/*
QM708E-070705 1
QM708E-070705 0
*/
--> 测试数据: @T1
declare @T1 table (Article varchar(20),車花 varchar(20),印膠 varchar(20))
insert into @T1
select 'QM708E-070705',null,null union all
select 'QM708X-070705',null,null union all
select 'QM708E-080101',null,null
--> 测试数据: @T2
declare @T2 table (Article varchar(20),Addon varchar(4),Value int)
insert into @T2
select 'QM708','印膠',2 union all
select 'QM708','車花',1 union all
select 'QM708E-070705','車花',1 union all
select 'QM708E-070705','印膠',null union all
select 'QM708X','印膠',null union all
select 'QM708X','車花',null
select a.article,
車花=case
when (select count(1) from @T2 where Article=a.Article and Addon='車花')>0 then (select value from @T2 where Article=a.Article and Addon='車花')
else
case when (select count(1) from @T2 where left(a.Article,6)=Article and Addon='車花')>0 then (select value from @T2 where left(a.Article,6)=Article and Addon='車花')
else
case when (select count(1) from @T2 where left(a.Article,5)=Article and Addon='車花')>0 then (select value from @T2 where left(a.Article,5)=Article and Addon='車花') else null end
end end,
印膠=case when (select count(1) from @T2 where Article=a.Article and Addon='印膠')>0 then (select value from @T2 where Article=a.Article and Addon='印膠') else
case when (select count(1) from @T2 where left(a.Article,6)=Article and Addon='印膠')>0 then (select value from @T2 where left(a.Article,6)=Article and Addon='印膠')
else case when (select count(1) from @T2 where left(a.Article,5)=Article and Addon='印膠')>0 then (select value from @T2 where left(a.Article,5)=Article and Addon='印膠') else null end
end end
from @t1 a
那到底是車花取null,还是印膠取null呢?
顺便说一下,我需要的是Update T1的记录
谢谢你,pt1314917
--> 测试数据: @T1
declare @T1 table (Article varchar(20),車花 varchar(20),印膠 varchar(20))
insert into @T1
select 'QM708E-070705',null,null union all
select 'QM708X-070705',null,null union all
select 'QM708E-080101',null,null
--> 测试数据: @T2
declare @T2 table (Article varchar(20),Addon varchar(4),Value int)
insert into @T2
select 'QM708','印膠',2 union all
select 'QM708','車花',1 union all
select 'QM708E-070705','車花',1 union all
select 'QM708E-070705','印膠',null union all
select 'QM708X','印膠',null union all
select 'QM708X','車花',null union all
select 'QM708X-080705',null,null
update @t1 set
車花=case
when (select count(1) from @T2 where Article=a.Article and Addon='車花')>0 then (select value from @T2 where Article=a.Article and Addon='車花')
else case when (select count(1) from @T2 where left(a.Article,6)=Article and Addon='車花')>0 then (select value from @T2 where left(a.Article,6)=Article and Addon='車花')
else case when (select count(1) from @T2 where left(a.Article,5)=Article and Addon='車花')>0 then (select value from @T2 where left(a.Article,5)=Article and Addon='車花') else null end
end end,
印膠=case when (select count(1) from @T2 where Article=a.Article and Addon='印膠')>0 then (select value from @T2 where Article=a.Article and Addon='印膠') else
case when (select count(1) from @T2 where left(a.Article,6)=Article and Addon='印膠')>0 then (select value from @T2 where left(a.Article,6)=Article and Addon='印膠')
else case when (select count(1) from @T2 where left(a.Article,5)=Article and Addon='印膠')>0 then (select value from @T2 where left(a.Article,5)=Article and Addon='印膠') else null end
end end
from @t1 a select * from @t1
--上面还没加那个过滤,这个加了。试试:--> 测试数据: @T1
declare @T1 table (Article varchar(20),車花 varchar(20),印膠 varchar(20))
insert into @T1
select 'QM708E-070705',null,null union all
select 'QM708X-070705',null,null union all
select 'QM708E-080101',null,null
--> 测试数据: @T2
declare @T2 table (Article varchar(20),Addon varchar(4),Value int)
insert into @T2
select 'QM708','印膠',2 union all
select 'QM708','車花',1 union all
select 'QM708E-070705','車花',1 union all
select 'QM708E-070705','印膠',null union all
select 'QM708X','印膠',3 union all
select 'QM708X','車花',7 union all
select 'QM708X-070705',null,null
update @t1 set
車花=case
when (select count(1) from @T2 where Article=a.Article and Addon='車花')>0 then (select value from @T2 where Article=a.Article and Addon='車花')
when (select count(1) from @T2 where Article=a.Article and isnull(addon,'')='' and isnull(value,0)=0)>0 then null
else case when (select count(1) from @T2 where left(a.Article,6)=Article and Addon='車花')>0 then (select value from @T2 where left(a.Article,6)=Article and Addon='車花')
else case when (select count(1) from @T2 where left(a.Article,5)=Article and Addon='車花')>0 then (select value from @T2 where left(a.Article,5)=Article and Addon='車花') else null end
end end,
印膠=case when (select count(1) from @T2 where Article=a.Article and Addon='印膠')>0 then (select value from @T2 where Article=a.Article and Addon='印膠')
when (select count(1) from @T2 where Article=a.Article and isnull(addon,'')='' and isnull(value,0)=0)>0 then null
else case when (select count(1) from @T2 where left(a.Article,6)=Article and Addon='印膠')>0 then (select value from @T2 where left(a.Article,6)=Article and Addon='印膠')
else case when (select count(1) from @T2 where left(a.Article,5)=Article and Addon='印膠')>0 then (select value from @T2 where left(a.Article,5)=Article and Addon='印膠') else null end
end end
from @t1 a select * from @t1
但你以上运行结果是QM708X-080705印胶带了QM708X的印胶value--3...--> 测试数据: @T1
declare @T1 table (Article varchar(20),車花 varchar(20),印膠 varchar(20))
insert into @T1
select 'QM708E-070705',null,null union all
select 'QM708X-070705',null,null union all
select 'QM708E-080101',null,null union all
select 'QM708X-080705',null,null
--> 测试数据: @T2
declare @T2 table (Article varchar(20),Addon varchar(4),Value int)
insert into @T2
select 'QM708','印膠',2 union all
select 'QM708','車花',1 union all
select 'QM708E-070705','車花',1 union all
select 'QM708E-070705','印膠',null union all
select 'QM708X','印膠',3 union all
select 'QM708X','車花',null union all
select 'QM708X-080705',null,null