如何获取 T-SQL 代码以查找重复项? [英] How get the T-SQL code to find duplicates?

问题描述

MS Access 有一个按钮来生成用于查找重复行的 sql 代码.不知道SQL Server 2005/2008 Managment Studio有没有这个.

MS Access has a button to generate sql code for finding duplicated rows. I don't know if SQL Server 2005/2008 Managment Studio has this.

1. 如果有，请指出在哪里

1. If it has, please point where

如果没有，请告诉我如何使用 T-SQL 助手来创建这样的代码.

If it has not, please tell me how can I have a T-SQL helper for creating code like this.

推荐答案

好吧，如果您的表中有整行作为重复项，那么您至少没有为该表设置主键，否则至少主键值会有所不同.

Well, if you have entire rows as duplicates in your table, you've at least not got a primary key set up for that table, otherwise at least the primary key value would be different.

但是，以下是如何构建 SQL 以获取一组列上的重复项:

However, here's how to build a SQL to get duplicates over a set of columns:

SELECT col1, col2, col3, col4
FROM table
GROUP BY col1, col2, col3, col4
HAVING COUNT(*) > 1

这将查找对于列 col1-col4 具有相同值组合且不止一次的行.

This will find rows which, for columns col1-col4, has the same combination of values, more than once.

例如，在下表中，第 2+3 行将是重复的:

For instance, in the following table, rows 2+3 would be duplicates:

PK    col1    col2    col3    col4    col5
1       1       2       3       4      6
2       1       3       4       7      7
3       1       3       4       7      10
4       2       3       1       4      5

两行在 col1-col4 列中共享共同的值，因此，通过该 SQL，被认为是重复的.展开列列表以包含您希望对其进行分析的所有列.

The two rows share common values in columns col1-col4, and thus, by that SQL, is considered duplicates. Expand the list of columns to contain all the columns you wish to analyze this for.

这篇关于如何获取 T-SQL 代码以查找重复项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！