时间戳

首页 > 解决方案 > 如何比较两个表并在sql中检索匹配和不匹配的数据？

sql - 如何比较两个表并在sql中检索匹配和不匹配的数据？

问题描述

我有两个具有不同列名的表，并且大多数值是相同的。我想要另一个表中匹配和不匹配的数据。

***
src-table
--------------------------------------------------------------------------------
eid  | ename      | email                   |  country
--------------------------------------------------------------------------------
 1     as           as@gmail.com                 india
 2     bs           bs@gmail.com                 usa
 3     cs           cs@gmail.com                 usa
 4     ds           ds@gmail.com                 india
--------------------------------------------------------------------------------
tgt_table
--------------------------------------------------------------------------------
eid  | ename      | email                   |  country
--------------------------------------------------------------------------------
 1     as           as@gmail.com                 india
 2     bs           b@gmail.com                 india
 3     cs           cs@gmail.com                 usa
 4     ds           d@gmail.com                 india
--------------------------------------------------------------------------------
expected output  for matched_data 
--------------------------------------------------------------------------------
src_coloumnname | src_data           | tgt_colomnname    | tgt_data
--------------------------------------------------------------------------------
eid                  1                   eid                  1
eid                  2                   eid                  2
eid                  3                   eid                  3
eid                  4                   eid                  4
ename                as                  ename                as 
ename                bs                  ename                bs
ename                cs                  ename                cs
ename                ds                  ename                ds
email            as@gmail.com            email             as@gmail.com
email            cs@gmail.com            email             cs@gmail.com
country          india                   country            india
country          usa                     country            usa
country          india                   country            india
----------------------------------------------------------------------------
***

标签： sqlsql-server

解决方案

可以一次使用一列来完成：

SELECT 'eid' AS match_column, l.eid AS src_value, r.eid AS tgt_value, CASE WHEN l.eid = r.eid THEN 'match' ELSE 'no match' END AS result
FROM table1 AS l
INNER JOIN table2 AS r ON l.eid = r.eid

UNION ALL

SELECT 'ename', l.ename, r.ename, CASE WHEN l.ename = r.ename THEN 'match' ELSE 'no match' END
FROM table1 AS l
INNER JOIN table2 AS r ON l.eid = r.eid

UNION ALL

SELECT 'email', l.email, r.email, CASE WHEN l.email = r.email THEN 'match' ELSE 'no match' END
FROM table1 AS l
INNER JOIN table2 AS r ON l.eid = r.eid

UNION ALL

SELECT 'country', l.country, r.country, CASE WHEN l.country = r.country THEN 'match' ELSE 'no match' END
FROM table1 AS l
INNER JOIN table2 AS r ON l.eid = r.eid

它匹配行匹配的列。它不检查任一表中缺少的行。

推荐阅读