有兩個表EmployeeA，EmployeeB其中包含員工資訊。兩者都有相同的列，如 Guid、FullName、Age、Salary 和 Address。這兩個表都有很多記錄，每個員工在每個表中最多有 1 條記錄。
EmployeeA是一種原始表，資料不能更改。所以它是只讀的，其中資料EmployeeA是從 API 匯入的。
最初，EmployeeB表中的資料通過 UI 來自EmployeeA表，用戶可以從 UI 修改它。所以EmployeeB可以修改資料。
我只需要在表中修改的那些列的更改結果EmployeeB。
例子 ：
Column Name EmployeeA's Data EMployeeB's Data
--------------------------------------------------
Salary $98000 $110000
Address ABC, USA PQR, USA
（FullName 和 Age 列沒有變化）。
回傳上述結果集的查詢會是什么樣子？
uj5u.com熱心網友回復：
連接兩個表并比較兩個表中的值。用于APPLY旋轉它
select a.GUID, c.ColumnName, c.EmpA, c.EmpB
from EmployeeA a
inner join EmployeeB b on a.GUID = b.GUID
cross apply
(
select ColumnName = 'FullName',
EmpA = a.FullName,
EmpB = b.FullName
where a.FullName <> b.FullName
union all
select ColumnName = 'Age',
EmpA = convert(varchar(100), a.Age),
EmpB = convert(varchar(100), b.Age)
where a.Age <> b.Age
union all
select ColumnName = 'Salary',
EmpA = convert(varchar(100), a.Salary),
EmpB = convert(varchar(100), b.Salary)
where a.Salary <> b.Salary
union all
select ColumnName = 'Address',
EmpA = a.Address, EmpB = b.Address
where a.Address <> b.Address
) c
注意：您可能希望更改上述查詢以處理空值
uj5u.com熱心網友回復：
沒有樣本資料，但可能類似于：
WITH
A AS
(
SELECT 1 "ID", 'NAME 1' "NAME", 100 "SALARY", 'ABC' "ADDRESS", 24 "AGE" FROM DUAL UNION ALL
SELECT 2 "ID", 'NAME 2' "NAME", 200 "SALARY", 'DEF' "ADDRESS", 30 "AGE" FROM DUAL UNION ALL
SELECT 3 "ID", 'NAME 3' "NAME", 300 "SALARY", 'GEH' "ADDRESS", 32 "AGE" FROM DUAL
),
B AS
(
SELECT 1 "ID", 'NAME 1' "NAME", 100 "SALARY", 'ABC' "ADDRESS", 24 "AGE" FROM DUAL UNION ALL
SELECT 2 "ID", 'NAME 2' "NAME", 220 "SALARY", 'DEF, DEF' "ADDRESS", 30 "AGE" FROM DUAL UNION ALL
SELECT 3 "ID", 'NAME 3' "NAME", 330 "SALARY", 'GEH' "ADDRESS", 32 "AGE" FROM DUAL
)
SELECT
A.NAME "NAME", 'SALARY' "COLUMN_NAME", To_Char(A.SALARY) "A_DATA", To_Char(B.SALARY) "B_DATA"
FROM
A
INNER JOIN
B ON(A.ID = B.ID)
WHERE
A.SALARY <> B.SALARY
UNION ALL
SELECT
A.NAME "NAME", 'ADDRESS' "COLUMN_NAME", A.ADDRESS "A_DATA", B.ADDRESS "B_DATA"
FROM
A
INNER JOIN
B ON(A.ID = B.ID)
WHERE
A.ADDRESS <> B.ADDRESS
ORDER BY 1
--
-- Result
-- NAME COLUMN A_DATA B_DATA
-- NAME 2 SALARY 200 220
-- NAME 2 ADDRESS DEF DEF, DEF
-- NAME 3 SALARY 300 330
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/484690.html