如何在Oracle中的表中找到重复值？

13 回答

• 1

  怎么样：

  SELECT <column>, count(*)
  FROM <table>
  GROUP BY <column> HAVING COUNT(*) > 1;
  

  要回答上面的例子，它看起来像：

  SELECT job_number, count(*)
  FROM jobs
  GROUP BY job_number HAVING COUNT(*) > 1;
  

  回复于 2024-04-29T13:24:03+08:00
• 4

  你也可以尝试这样的方法来列出表格中的所有重复值，例如reqitem

  SELECT count(poid) 
  FROM poitem 
  WHERE poid = 50 
  AND rownum < any (SELECT count(*)  FROM poitem WHERE poid = 50) 
  GROUP BY poid 
  MINUS
  SELECT count(poid) 
  FROM poitem 
  WHERE poid in (50)
  GROUP BY poid 
  HAVING count(poid) > 1;
  

  回复于 2024-04-29T13:24:03+08:00
• 16

  其他方式：

  SELECT *
  FROM TABLE A
  WHERE EXISTS (
    SELECT 1 FROM TABLE
    WHERE COLUMN_NAME = A.COLUMN_NAME
    AND ROWID < A.ROWID
  )
  

  当 column_name 上有索引时，工作正常（足够快） . 并且它是删除或更新重复行的更好方法 .

  回复于 2024-04-29T13:24:03+08:00
• 48

  如果您不需要知道重复的实际数量，则不需要在返回的列中包含计数 . 例如

  SELECT column_name
  FROM table
  GROUP BY column_name
  HAVING COUNT(*) > 1
  

  回复于 2024-04-29T13:24:03+08:00
• 4

  以下是执行此操作的SQL请求：

  select column_name, count(1)
  from table
  group by column_name
  having count (column_name) > 1;
  

  回复于 2024-04-29T13:24:03+08:00
• -1

  干

  select count(j1.job_number), j1.job_number, j1.id, j2.id
  from   jobs j1 join jobs j2 on (j1.job_numer = j2.job_number)
  where  j1.id != j2.id
  group by j1.job_number
  

  会给你重复的行ID .

  回复于 2024-04-29T13:24:03+08:00
• 7

  我知道这是一个旧线程，但这可能对某人有所帮助 .

  如果您需要打印表格的其他列，同时检查下面的重复使用：

  select * from table where column_name in
  (select ing.column_name from table ing group by ing.column_name having count(*) > 1)
  order by column_name desc;
  

  如果需要，还可以在where子句中添加一些额外的过滤器 .

  回复于 2024-04-29T13:24:03+08:00
• 31

  最简单的我能想到：

  select job_number, count(*)
  from jobs
  group by job_number
  having count(*) > 1;
  

  回复于 2024-04-29T13:24:03+08:00
• 5

  1. solution

  select * from emp
      where rowid not in
      (select max(rowid) from emp group by empno);
  

  回复于 2024-04-29T13:24:03+08:00
• -1

  我通常使用Oracle Analytic函数ROW_NUMBER() .

  假设您要检查有关在列（ c1 ， c2 ， c3 ）上构建的唯一索引或主键的重复项 . 然后你会这样，调出 ROWID 的行，其中 ROW_NUMBER() 带来的行数为 >1 ：

  Select * From Table_With_Duplicates
        Where Rowid In
                      (Select Rowid
                         From (Select Rowid,
                                      ROW_NUMBER() Over (
                                              Partition By c1 || c2 || c3
                                              Order By c1 || c2 || c3
                                          ) nbLines
                                 From Table_With_Duplicates) t2
                        Where nbLines > 1)
  

  回复于 2024-04-29T13:24:03+08:00
• 0

  SELECT   SocialSecurity_Number, Count(*) no_of_rows
  FROM     SocialSecurity 
  GROUP BY SocialSecurity_Number
  HAVING   Count(*) > 1
  Order by Count(*) desc
  

  回复于 2024-04-29T13:24:03+08:00
• 522

  select column_name, count(column_name)
  from table
  group by column_name
  having count (column_name) > 1;
  

  回复于 2024-04-29T13:24:03+08:00
• 1

  如果多列标识唯一行（例如关系表），则可以使用以下内容

  使用行ID，例如emp_dept（empid，deptid，startdate，enddate）假设empid和deptid是唯一的并且在那种情况下识别行

  select oed.empid, count(oed.empid) 
  from emp_dept oed 
  where exists ( select * 
                 from  emp_dept ied 
                  where oed.rowid <> ied.rowid and 
                         ied.empid = oed.empid and 
                        ied.deptid = oed.deptid )  
          group by oed.empid having count(oed.empid) > 1 order by count(oed.empid);
  

  如果这样的表有主键，那么使用主键而不是rowid，例如id是pk然后

  select oed.empid, count(oed.empid) 
  from emp_dept oed 
  where exists ( select * 
                 from  emp_dept ied 
                  where oed.id <> ied.id and 
                         ied.empid = oed.empid and 
                        ied.deptid = oed.deptid )  
          group by oed.empid having count(oed.empid) > 1 order by count(oed.empid);
  

  回复于 2024-04-29T13:24:03+08:00