SQL Server-比较2个表中同一列中的数据，而不检查是否相等 [英] SQL Server - Compare 2 tables for data in the same columns without checking for equality
问题描述
我在这里之前问了这个问题，但答案实际上并不是我想要的。
I asked this question before here, but the answer actually wasn't what I was looking for.
假设我在SQL Server（2012）数据库中有以下两个表：
Suppose I have the following two tables in my SQL Server (2012) DB:
Tbl1：
ID: Col1: Col2: Col3:
1 Val11 Val21 Val31
2 <NULL> Val21 <NULL>
3 Val11 <NULL> Val31
4 Val11 <NULL> Val31
Tbl2：
ID: Col1: Col2: Col3:
1 Val12 Val22 Val32
2 <NULL> Val22 <NULL>
3 <NULL> <NULL> Val32
5 <NULL> <NULL> Val32
在这一点上，我想看到的是：
And, at this point, all I want to see is:
- 在一个表中但不在另一个表中的任何行（基于
IDpk） - 如果两个表中都包含
ID，则将填充相同的列（还不特别关心值）。
- Any rows that are in one table but not the other (based on
IDpk) - If the
IDis in both tables, then are the same columns populated (not caring specifically about the values yet).
我只是想提出一个SQL来让我知道哪个 ID s有差异。
I'm just trying to come up with a SQL to just let me know which IDs have discrepancies.
我的理想输出如下：
Tbl1_ID: Tbl2_Id: Discrepancy:
1 1 0
2 2 0
3 3 1
4 <NULL> 1
<NULL> 5 1
到目前为止，我的测试SQL是：
My testing SQL so far is this:
DECLARE
@Tbl1 TABLE (ID INT, Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 VARCHAR(10))
;
DECLARE
@Tbl2 TABLE (ID INT, Col1 VARCHAR(10), Col2 VARCHAR(10), Col3 VARCHAR(10))
;
INSERT INTO @Tbl1 (ID, Col1, Col2, Col3)
VALUES
(1, 'Val11', 'Val21', 'Val31')
,(2, NULL , 'Val21', NULL)
,(3, 'Val11', NULL, 'Val31')
,(4, 'Val11', NULL, 'Val31')
;
INSERT INTO @Tbl2 (ID, Col1, Col2, Col3)
VALUES
(1, 'Val12', 'Val22', 'Val32')
,(2, NULL , 'Val22', NULL)
,(3, NULL, NULL, 'Val32')
,(5, NULL, NULL, 'Val32')
;
SELECT
[@Tbl1].ID AS Tbl1_ID
,[@Tbl2].ID AS Tbl2_ID
, -- Some kind of comparison to let me know if all columns are populated the same
AS Discrepancy
FROM
@Tbl1
FULL JOIN @Tbl2
ON [@Tbl1].ID = [@Tbl2].ID
如上所示问题的答案，提出的解决方案（我没有对其进行足够好的检查）正在做 ISNULL（Tbl1.Col1，xx）= ISNULL（Tbl2.Col1，xx）我正在寻找的列的一种比较。那里的问题是，实际上是相互对照检查两个表的值。我想做的就是检查它们是否都已填充，而无需进行价值比较。
As you can see in the previous question's answer, the solution proposed (and I didn't check it well enough) was doing an ISNULL(Tbl1.Col1, xx) = ISNULL(Tbl2.Col1, xx) kind of comparison for the columns I'm looking for. The problem there is that it is, in fact, checking the values of the two tables against each other. All I want to do is check if they are both populated or not, without any need to do a value-comparison.
我知道我可以用一些方法来完成此任务的行，当Tbl1.Col1为NULL则为1 ELSE 0 END =的情况为Tcase，当Tbl2.Col1为NULL则为1 ELSE 0 END ，但我希望有一个更好的例子
I know I could accomplish this with something along the lines of CASE WHEN Tbl1.Col1 is NULL THEN 1 ELSE 0 END = CASE WHEN Tbl2.Col1 IS NULL THEN 1 ELSE 0 END, but I'm hoping there is a nicer way to do this since I am checking a lot of columns.
是否有一些好的方法来完成此操作？
Is there some good way to accomplish this?
谢谢！
推荐答案
您可以使用：
SELECT
t1.ID AS Tbl1_ID
,t2.ID AS Tbl2_ID
,CASE WHEN NOT EXISTS (
-- here use template and put column list from table 1
SELECT CASE WHEN t1.Col1 IS NOT NULL THEN -1 ELSE 0 END,
CASE WHEN t1.Col2 IS NOT NULL THEN -1 ELSE 0 END,
CASE WHEN t1.Col3 IS NOT NULL THEN -1 ELSE 0 END
INTERSECT
-- here use template and put column list from table 2
SELECT CASE WHEN t2.Col1 IS NOT NULL THEN -1 ELSE 0 END,
CASE WHEN t2.Col2 IS NOT NULL THEN -1 ELSE 0 END,
CASE WHEN t2.Col3 IS NOT NULL THEN -1 ELSE 0 END)
THEN 1 ELSE 0 END AS Discrepancy
FROM @Tbl1 t1
FULL JOIN @Tbl2 t2
ON t1.ID = t2.ID;
Rextester.com Demo
它是 IS DISTINCT FROM 。如果您关心实际值，则：
It is a variant of IS DISTINCT FROM. If you care about actual values then:
SELECT
t1.ID AS Tbl1_ID
,t2.ID AS Tbl2_ID
,CASE WHEN NOT EXISTS (
SELECT t1.Col1, t1.Col2, t1.Col3
INTERSECT
SELECT t2.Col1, t2.Col2, t2.Col3)
THEN 1 ELSE 0 END AS Discrepancy
FROM @Tbl1 t1
FULL JOIN @Tbl2 t2
ON t1.ID = t2.ID;
这篇关于SQL Server-比较2个表中同一列中的数据，而不检查是否相等的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
