我有两个tables
的帐户代码如下,
table 1:
account
50000
50006
50015
50105
50150
50155
50165
table 2:
Account
50000
50010
50140
50105
50150
50155
50165
我需要加入这两个
tables
。如果table-1
的任何帐户代码与table-2
不匹配,那么我已将表1帐户代码隐式更改为表2帐户代码。我做了以下事情,
SELECT T1.Account, T2.Account
FROM table1 t1
INNER JOIN table2 t2
on (t2.account = CASE t1 .account
WHEN 50015 THEN 50010
WHEN 50006 THEN 50140
ELSE t1 .account
END )
但是我只有匹配的代码作为输出,
account Account
50000 50000
50105 50105
50150 50150
50155 50155
50165 50165
我没有收到不匹配的帐户代码,即
(50006 and 50015)
。谁能帮我找出问题所在?我的预期输出是
account Account
50000 50000
50006 50140
50015 50010
50105 50105
50150 50150
50155 50155
50165 50165
谢谢您的帮助
最佳答案
使用CASE
然后DISTINCT
数据,这将为您提供通用解决方案
-- table1
declare @table1 table
(account bigint)
insert into @table1 values (50000)
insert into @table1 values (50006)
insert into @table1 values (50015)
insert into @table1 values (50105)
insert into @table1 values (50150)
insert into @table1 values (50155)
insert into @table1 values (50165)
-- table2
declare @table2 table
(account bigint)
insert into @table2 values (50000)
insert into @table2 values (50010)
insert into @table2 values (50140)
insert into @table2 values (50105)
insert into @table2 values (50150)
insert into @table2 values (50155)
insert into @table2 values (50165)
-- QUERY
select distinct t1.account as Account1,
Account2 = case
when t1.account = t2.account then t2.account else t1.account
end
from @table1 t1, @table2 t2
结果
Account1 Account2
50000 50000
50006 50006
50015 50015
50105 50105
50150 50150
50155 50155
50165 50165
评论后编辑-好吧,这是我们要求的一部分。我需要更新与50140中的50006帐户代码相对应的金额,依此类推...
select distinct t1.account as Account1,
Account2 = case
when t1.account = 50006 then 50140
when t1.account = 50015 then 50010
else t1.account end
from @table1 t1 , @table2 t2
结果
Account1 Account2
50000 50000
50006 50140
50015 50010
50105 50105
50150 50150
50155 50155
50165 50165