SQL 在列中查找重复条目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4522431/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find duplicate entries in a column
提问by anwarma
I am writing this query to find duplicate CTN Records in table1. So my thinking is if the CTN_NO appears more than twice or higher , I want it shown in my SELECT * statement output on top.
我正在编写此查询以在表 1 中查找重复的 CTN 记录。所以我的想法是，如果 CTN_NO 出现两次以上，我希望它显示在我的 SELECT * 语句输出中。
I tried the following sub-query logic but I need pulls
我尝试了以下子查询逻辑，但我需要拉
SELECT *
table1
WHERE S_IND='Y'
and CTN_NO = (select CTN_NO
from table1
where S_IND='Y'
and count(CTN_NO) < 2);
order by 2
回答by OMG Ponies
Using:
使用：
SELECT t.ctn_no
FROM YOUR_TABLE t
GROUP BY t.ctn_no
HAVING COUNT(t.ctn_no) > 1
...will show you the ctn_novalue(s) that have duplicates in your table. Adding criteria to the WHERE will allow you to further tune what duplicates there are:
...将向您显示ctn_no表中重复的值。向 WHERE 添加条件将允许您进一步调整有哪些重复项：
SELECT t.ctn_no
FROM YOUR_TABLE t
WHERE t.s_ind = 'Y'
GROUP BY t.ctn_no
HAVING COUNT(t.ctn_no) > 1
If you want to see the other column values associated with the duplicate, you'll want to use a self join:
如果要查看与重复项关联的其他列值，则需要使用自联接：
SELECT x.*
FROM YOUR_TABLE x
JOIN (SELECT t.ctn_no
FROM YOUR_TABLE t
GROUP BY t.ctn_no
HAVING COUNT(t.ctn_no) > 1) y ON y.ctn_no = x.ctn_no
回答by Chandu
Try this query.. It uses the Analytic function SUM:
试试这个查询。它使用分析函数 SUM：
SELECT * FROM
(
SELECT SUM(1) OVER(PARTITION BY ctn_no) cnt, A.*
FROM table1 a
WHERE s_ind ='Y'
)
WHERE cnt > 2
Am not sure why you are identifying a record as a duplicate if the ctn_no repeats more than 2 times. FOr me it repeats more than once it is a duplicate. In this case change the las part of the query to WHERE cnt > 1
如果 ctn_no 重复超过 2 次，我不确定为什么将记录识别为重复。对我来说，它重复不止一次，它是重复的。在这种情况下，将查询的 las 部分更改为WHERE cnt > 1
