基于单个列的oracle重复行 [英] oracle duplicate rows based on a single column

问题描述

如何找到基于单列的重复行。我在oracle中有一个表，它的数据如下所示，它有重复的。我正在尝试选择并查看具有重复员工ID的所有行，如下所述

How can I find out duplicate rows based on a single column. I have a table in oracle which has data as given below and it has duplicates. I'm trying to select and view all rows with duplicate employee ids as explained below

EMP表：

EmpId Fname Lname Mname Jobcode Status exp_date

1      Mike Jordan A    IT      W      12/2014
1      Mike Jordan A    IT      A      12/2014
2      Angela ruth C    sales   P      12/2015
2      Angela ruth C    IT      W      12/2015
3      Kelly  Mike B    sales   W      12/2015

从上表中我想选择所有重复emp的行，如下面的

From the above table i want to select all rows which duplicate empids such as below

EmpId Fname Lname Mname Jobcode Status exp_date

1      Mike Jordan A    IT      W      12/2014
1      Mike Jordan A    IT      A      12/2014
2      Angela ruth C    sales   P      12/2015
2      Angela ruth C    IT      W      12/2015

我该怎么做这个？谢谢！

How can I do this? thank you!

推荐答案

SELECT  a.*
FROM    TableName a
        INNER JOIN
        (
            SELECT  EmpID
            FROM    TableName
            GROUP   BY EmpID
            HAVING  COUNT(*) > 1
        ) b ON a.EmpID = b.EmpID

• SQLFiddle演示


• SQLFiddle Demo

另一种方式，虽然我更喜欢上述，但是要使用 IN

Another way, although I prefer above, is to use IN

SELECT  a.*
FROM    TableName a
WHERE   EmpId IN
        (
            SELECT  EmpId
            FROM    TableName
            GROUP   BY EmpId
            HAVING  COUNT(*) > 1
        ) 







• < a href =http://www.sqlfiddle.com/#!4/a478e/7 =noreferrer> SQLFiddle Demo


• SQLFiddle Demo

这篇关于基于单个列的oracle重复行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！