在列中查找重复条目

2022-01-23 00:00:00 sql subquery oracle

我正在编写此查询以在 table1 中查找重复的 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

相关文章