比较具有重复值的行 [英] Compare rows with duplicate values

问题描述

我想在表格中找到重复的行。我可以找到下面列出的重复项数，但我想在结果中包含所有行，以便我可以比较其他列中的值以找出我应该删除的内容。我希望结果显示
计数列。此查询似乎只显示一次带有计数的行。 我该怎么办？我想使用最有效的ansi标准sql。

I want to find duplicate rows in a table. I can find a count of duplicates as listed below but I want to include all of the rows in the result so that I can compare values in other columns to find out which I should delete. I want the result to show the Count column. This query seems to only show the row once with a count.  How would I do that? I want to use the most ansi standard sql that will work.

推荐答案

您好Tom ERP，

Hi Tom ERP,

CTE和排名函数可以为您的方案提供帮助。

CTE and ranking functions could help for your scenario.

请参见下文：

DECLARE @tbl TABLE (ID INT NOT NULL
            , State VARCHAR(30) NOT NULL
            , City VARCHAR(30) NOT NULL
            , Population INT NOT NULL);

INSERT INTO @tbl(ID, State, City, Population)
VALUES (1, 'FL','Miami', 100)
   , (2, 'FL','Orlando', 200)
   , (1, 'FL','Miami', 770)
   , (3, 'TX','Dallas', 540);


WITH rs AS 
(
   SELECT *, 
      ROW_NUMBER() OVER(PARTITION BY ID, State, City ORDER BY Population) AS rn
   FROM @tbl
)
SELECT * FROM rs;
--WHERE rn = 1;

这篇关于比较具有重复值的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！