在列中查找重复的条目 [英] Find duplicate entries in a column
本文介绍了在列中查找重复的条目的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在编写此查询以在表1中查找重复的CTN记录.所以我的想法是,如果CTN_NO出现两次以上或更高,我希望它显示在我的SELECT *语句输出的顶部.
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.
我尝试了以下子查询逻辑,但我需要拉动
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
推荐答案
使用:
SELECT t.ctn_no
FROM YOUR_TABLE t
GROUP BY t.ctn_no
HAVING COUNT(t.ctn_no) > 1
...将显示表中重复的ctn_no
值.在WHERE中添加条件将使您可以进一步调整存在哪些重复项:
...will show you the ctn_no
value(s) that have duplicates in your table. Adding criteria to the WHERE will allow you to further tune what duplicates there are:
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
这篇关于在列中查找重复的条目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文