表之间更改摘要的SQL查询

Question

有两个包含员工信息的表EmployeeA和EmployeeB。两者都有相同的列，如Guid、FullName、Age、Salary和Address。这两个表都有许多记录，每个员工在每个表中最多有1条记录。

EmployeeA是一种原始表，数据不能更改。因此它是从API导入CCD_ 4中的数据的只读。

最初EmployeeB表中的数据通过UI来自EmployeeA表，用户可以从UI中进行修改。因此EmployeeB可以修改数据。

我只需要EmployeeB表中修改的列的更改结果。

示例：

Column Name    EmployeeA's Data  EMployeeB's Data
--------------------------------------------------
Salary         $98000            $110000
Address        ABC, USA          PQR, USA

(FullName和Age列中没有更改)。

返回上述结果集的查询会是什么样子？

Answer 1

连接两个表并比较两个表的值。使用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

注意：您可能需要更改以上查询以处理空值

Answer 2

没有样本数据，但它可能类似于：

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