时间戳

首页 > 解决方案 > 如何显示所有重复值

mysql - 如何显示所有重复值

问题描述

无论如何我可以使用 group by 显示 mysql 中的所有重复值并拥有

+---------+--------------+------------+--------+----------+----------+---------+
| ENumber | EmpName      | Birthdate  | Gender | Address  | Salary   | DNumber |
+---------+--------------+------------+--------+----------+----------+---------+
| E001    | GSInocencio  | 1988-01-15 | F      | Munoz    | 18000.00 | D005    |
| E002    | EAVillanueva | 1988-04-20 | F      | Munoz    | 23000.00 | D003    |
| E003    | ALedesma     | 1988-05-25 | M      | CLSU     | 21000.00 | D002    |
| E004    | APGamilla    | 1991-10-15 | F      | Maligaya | 25000.00 | D001    |
| E005    | ACTolentino  | 1989-02-20 | F      | Maligaya | 30000.00 | D002    |
| E006    | ANVillasoto  | 1999-01-05 | M      | CLSU     | 15000.00 | D004    |
| E007    | JPPalada     | 1997-01-10 | M      | Munoz    | 21000.00 | D001    |
| E008    | NTNicodemus  | 1995-04-15 | F      | Maligaya | 22000.00 | D003    |
+---------+--------------+------------+--------+----------+----------+---------+

我想在 DNumber 中显示所有重复值

  +---------+--------------+------------+--------+----------+----------+---------+
| ENumber | EmpName      | Birthdate  | Gender | Address  | Salary   | DNumber |
+---------+--------------+------------+--------+----------+----------+---------+
| E004    | APGamilla    | 1991-10-15 | F      | Maligaya | 25000.00 | D001    |
| E007    | JPPalada     | 1997-01-10 | M      | Munoz    | 21000.00 | D001    |
| E003    | ALedesma     | 1988-05-25 | M      | CLSU     | 21000.00 | D002    |
| E005    | ACTolentino  | 1989-02-20 | F      | Maligaya | 30000.00 | D002    |
| E002    | EAVillanueva | 1988-04-20 | F      | Munoz    | 23000.00 | D003    |
| E008    | NTNicodemus  | 1995-04-15 | F      | Maligaya | 22000.00 | D003    |

标签： mysqlsqlsubqueryinner-joinhaving-clause

解决方案

display all the duplicate values in mysql using group by and having

There is more than one way to do it, but if you want to use group by and having, then you can join the table with an aggregate subquery that identifies the duplicates, as follows:

select t.*
from mytable t
inner join (
    select dnumber
    from mytable
    group by dnumber
    having count(*) > 1
) x on x.dnumber = t.dnumber
order by t.dnumber, t.enumber

推荐阅读