sql - 比较两个表并找出列值的差异
问题描述
我想比较两个表(相同的列)并找出哪些列值发生了变化。
这是示例数据的示例。
employee_original表有 6 列。
CREATE TABLE [dbo].[employee_original](
[emp_id] [int] IDENTITY(1,1) NOT NULL,
[first_name] [varchar](100) NOT NULL,
[last_name] [varchar](100) NOT NULL,
[salary] int NOT NULL,
[city] [varchar](20) NOT NULL,
[department] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[emp_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO employee_original VALUES ( 'Julia', 'Schultz', 100, 'New York', 'Tech');
INSERT INTO employee_original VALUES ( 'Vincent', 'Trantow', 200, 'Moscow', 'HR');
INSERT INTO employee_original VALUES ( 'Whitney ', 'Pouros', 500, 'Miami', 'Accounting');
INSERT INTO employee_original VALUES ( 'Chandler', 'Osinski', 10, 'Singapore', 'Purchasing');
INSERT INTO employee_original VALUES ( 'Sydnie', 'Green', 700, 'Ireland', 'Operations');
INSERT INTO employee_original VALUES ( 'Josefa', 'Anderson', 800, 'Berlin', 'Purchase');
INSERT INTO employee_original VALUES ( 'Brayan', 'Bergstrom', 900, 'New York', 'Operations');
INSERT INTO employee_original VALUES ( 'Shyanne', 'Kris', 900, 'New York', 'Sales');
employee_modified具有相同的员工,但少数员工的某些属性已更改。
CREATE TABLE [dbo].[employee_modified](
[emp_id] [int] IDENTITY(1,1) NOT NULL,
[first_name] [varchar](100) NOT NULL,
[last_name] [varchar](100) NOT NULL,
[salary] int NOT NULL,
[city] [varchar](20) NOT NULL,
[department] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
[emp_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO employee_modified VALUES ( 'Julia', 'Schultz', 100, 'New York', 'Tech');
INSERT INTO employee_modified VALUES ( 'Vincent', 'Wyman', 500, 'Moscow', 'HR');
INSERT INTO employee_modified VALUES ( 'Whitney ', 'Pouros', 500, 'Miami', 'Sales');
INSERT INTO employee_modified VALUES ( 'Chandler', 'Osinski', 10, 'Singapore', 'Purchasing');
INSERT INTO employee_modified VALUES ( 'Sydnie', ' Cartwright', 900, 'Ireland', 'Operations');
INSERT INTO employee_modified VALUES ( 'Joseph', 'Anderson', 800, 'Berlin', 'Purchase');
INSERT INTO employee_modified VALUES ( 'Bryan', 'Bergstrom', 900, 'Naples', 'Operations');
INSERT INTO employee_modified VALUES ( 'Shyanne', 'Jakubowski', 900, 'New York', 'Accounting');
我正在寻找可以告诉我哪个员工更改了哪个字段的结果。例如emp_id =2 有姓氏和薪水变化。所以输出应该是这样的:
emp_id attribute orignial_value new_value
2 last_name Trantow Wyman
2 salary 200 500
这是我到目前为止所尝试的:
(1) 加入表格并找出改变的地方:
DROP TABLE IF EXISTS #temp;
SELECT distinct
o.emp_id,
o.first_name [original_first_name], m.first_name [modified_first_name],
o.last_name [original_last_name], m.last_name [modified_last_name],
o.salary [original_salary], m.salary [modified_salary],
o.city [original_city], m.city [modified_city],
o.department [original_department], m.department [modified_department]
into #temp from
[dbo].[employee_original] o inner join [dbo].[employee_modified] m on o.emp_id = m.emp_id
select * from #temp
给我
(2) 自加入#temp
并找出哪些属性发生了变化。
-- All Last Name Changes.
select distinct t1.emp_id, t1.original_last_name, t2.modified_last_name
from #temp t1
inner join #temp t2 on t1.emp_id = t2.emp_id
where t1.original_last_name <> t2.modified_last_name
-- All Department changes
select distinct t1.emp_id, t1.original_department, t2.modified_department
from #temp t1
inner join #temp t2 on t1.emp_id = t2.emp_id
where t1.original_department <> t2.modified_department
关于如何达到我想要的结果的任何指示。
解决方案
您可以使用以下代码取消透视所有可能的更改
SELECT
o.emp_id,
v.column_name,
v.old_value,
v.new_value
FROM employee_original o
JOIN employee_modified m ON o.emp_id = m.emp_id
CROSS APPLY (
SELECT 'first_name', CAST(o.first_name AS nvarchar(max)), CAST(m.first_name AS nvarchar(max))
WHERE o.first_name <> m.first_name
UNION ALL
SELECT 'last_name', o.last_name, m.last_name
WHERE o.last_name <> m.last_name
UNION ALL
SELECT 'salary', o.salary, m.salary
WHERE o.salary <> m.salary
UNION ALL
SELECT 'city', o.city, m.city
WHERE o.city <> m.city
UNION ALL
SELECT 'department', o.department, m.department
WHERE o.department <> m.department
) v(column_name, old_value, new_value);
推荐阅读
- mybatis - 为什么mybatis insert 不返回最后一个insert id?
- r - 基于 dplyr 函数从现有数据框创建列表
- .net - 部署到服务器时出现 CORS 问题
- node.js - 将 express-sslify 安装到 MERN 项目后出现 net::ERR_SSL_PROTOCOL_ERROR 错误
- c - 即使我有 clobbers,GCC 也不会在我的内联 asm 函数调用周围推送寄存器
- node.js - 从 bacnet 模拟器读取数据时出错:错误:ERR_TIMEOUT 使用 bacstack lib(nodejs)
- phpstorm - 如何在使用 DDEV 处理本地项目时忽略 Xdebug + PhpStorm 中的 phpstatus.php 连接?
- javascript - JavaScript Blob 返回损坏的 PDF
- spring - 如何使用 InMemoryUserDetailsManager 后自动配置?
- android - 在 Activity 中以编程方式添加 CompoundView 不起作用