提问人：Anson 提问时间：11/12/2021 最后编辑：marc_sAnson 更新时间：11/13/2021 访问量：301
如何在 SQL 中比较两个没有唯一标识符的表
How to compare two tables without unique identifier in SQL
问：
我有两个表具有相同的结构，但没有唯一标识符。如何比较这两个表
我尝试使用行号来比较它们。代码如下
WITH source AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY Customer Account address) AS RowN,
Customer Account address
FROM
old
)
WITH target AS
(
SELECT
ROW_NUMBER() OVER(ORDER BY Customer Account address) AS RowN,
Customer Account address
FROM
New
)
SELECT
s.address, t.address
FROM
source s
JOIN
traget t ON s.RowN = t.RowN
WHERE
s.Customer != t.Customer
OR s.Account != t.Account
OR s.address != t.address
除了结果：
s.address t.address
---------------------
BB1 BB2
但是我收到一个错误
关键字“WITH”附近的语法不正确
Microsoft SQL Server 版本：Microsoft SQL Server 2017
答：
2赞
marcothesane
11/12/2021
#1
实际上，您可以使用表运算符：
像这里：EXCEPT
WITH
old_table(cust,accnt,addr) AS (
SELECT 'AAAA',101,'AA1'
UNION ALL SELECT 'BBBB',102,'BB1'
UNION ALL SELECT 'CCCC',102,'BB1'
)
,
new_table AS (
SELECT 'AAAA',101,'AA1'
UNION ALL SELECT 'BBBB',102,'BB1'
UNION ALL SELECT 'CCCC',102,'BB2'
)
(
SELECT * FROM old_table
EXCEPT
SELECT * FROM new_table
)
UNION ALL
(
SELECT * FROM new_table
EXCEPT
SELECT * FROM old_table
)
;
-- out cust | accnt | addr
-- out ------+-------+------
-- out CCCC | 102 | BB1
-- out CCCC | 102 | BB2
评论
0赞
Anson
11/13/2021
嗨，Marcothesane，我的表格有一百万条记录。我们有办法比较这两个表吗
1赞
eshirvana
11/12/2021
#2
你错过了很多逗号;)
WITH source AS
(
SELECT ROW_NUMBER() OVER(ORDER BY Customer ,Account, address) AS RowN,
Customer, Account ,address
FROM old
)
, target AS
(
SELECT ROW_NUMBER() OVER(ORDER BY Customer, Account ,address) AS RowN,
Customer, Account, address
FROM New
)
Select s.address , t.address
from source s
join traget t on s.RowN =t.RowN
where s.Customer <> t.Customer
or s.Account <> t.Account
or s.address <> t.address
评论
0赞
Sean Lange
11/12/2021
这仍然缺少很多逗号。查看子句的顺序。;)
1赞
eshirvana
11/12/2021
@SeanLange哈哈，谢谢;)
0赞
Anson
11/13/2021
嗨，eshirvana，谢谢!!
评论
WITHORDER BYCustomer Account address也无效。事实上，上面充斥着语法错误。