比较SQL Server中的两行。

Scenario

场景

A very large size query returns a lot of fields from multiple joined tables. Some records seem to be duplicated. You accomplish some checks, some grouping. You focus on a couple of records for further investigation. Still, there are too much fields to check each value.

一个非常大的查询从多个已连接的表返回许多字段。有些记录似乎是重复的。你完成一些检查，一些分组。你把重点放在两个记录上，以便进一步调查。不过，仍然有太多字段要检查每个值。

Question

问题

Is there any built-in function that compares two records, returning TRUE if the records match, otherwise FALSE and the set of not matching fields?

是否有一个内置函数来比较两个记录，如果记录匹配，返回TRUE，否则为FALSE和不匹配字段的集合?

3 个解决方案

#1

The CHECKSUM function should help identify matching rows

校验和函数应该有助于识别匹配的行

 SELECT CHECKSUM(*) FROM table

#2

May be this is what you are looking for:

也许这就是你想要的:

SELECT * FROM YourTable 
GROUP BY <<ColumnList>>
HAVING COUNT(*) > 1

Just developing on the suggestion provide by Podiluska to find the records which are duplicates

根据Podiluska提供的建议去寻找重复的记录

SELECT CHECKSUM(*) 
FROM YourTable 
GROUP BY CHECKSUM(*) 
HAVING COUNT(*) > 1

#3

I would suggest that use the hashbytes function to compare rows.It is better than checksum.

我建议使用hashbytes函数来比较行。它比校验和好。

What about creating a row_number and parttion by all the columns and then select all the rows which are having the rn as 2 and above? This is not slow method as well as it will give you perfect data and will give the full row's data which is being duplicated.I would go with this method instead of relying on all the hashing techniques..

创建一个row_number和parttion的所有列，然后选择所有的rn为2和以上的行?这并不是慢的方法，因为它将提供完美的数据，并将提供正在被复制的整行数据。我将采用这种方法，而不是依赖所有的散列技术。

#1