我有這兩張表：
我想在一張表中捕捉這兩張表之間的區別。源表已更改 ID 1 和 ID 3 的列，具有不同的狀態。
我的預期表格如下：
我試圖用MERGET-SQL 中的函式來做，但我不能正確地做。在我的真實資料中，我有大約 60 列的狀態，所以我想學習如何去做。
uj5u.com熱心網友回復：
這是一種方法
drop table if exists #expected
create table #expected (
id int,
changedcolumn varchar(50),
dateofchange datetime);
with target_t as(
SELECT 1 as id , 'open' as status1 ,'open' as status2, 'closed' as status3 union all
SELECT 2 as id , 'closed' as status1 ,'open' as status2, 'open' as status3 union all
SELECT 3 as id , 'open' as status1 ,'open' as status2, 'closed' as status3 )
,source_t as (
SELECT 1 as id , 'open' as status1 ,'closed' as status2, 'open' as status3 union all
SELECT 2 as id , 'closed' as status1 ,'open' as status2, 'open' as status3 union all
SELECT 3 as id , 'open' as status1 ,'open' as status2, 'open' as status3 )
INSERT INTO #expected
SELECT tb.*
FROM(
SELECT
t.id
,'Status1' as changedcolumn
,getdate() AS dateofchange
FROM target_t t
INNER JOIN source_t s
ON t.id = s.id
WHERE t.status1 <> s.status1
union all
SELECT
t.id
,'Status2' as changedcolumn
,getdate() AS dateofchange
FROM target_t t
INNER JOIN source_t s
ON t.id = s.id
WHERE t.status2 <> s.status2
union all
SELECT
t.id
,'Status3' as changedcolumn
,getdate() AS dateofchange
FROM target_t t
INNER JOIN source_t s
ON t.id = s.id
WHERE t.status3 <> s.status3
) tb
LEFT JOIN (
SELECT *
,row_number()over(partition by id, changedcolumn order by dateofchange desc) as rn
FROM #expected
) x
ON x.id = tb.id
AND x.rn = 1
WHERE x.id IS NULL
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/491567.html
上一篇：從跨列比較中排除空值