比较具有重复值的行 [英] 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屋!
查看全文