Mysql获取一个值的副本 - mysql get duplicates of one value

mysql get duplicates of one value

本文关键字：一个副本获取 Mysql | 更新日期: 2023-09-27

在mysql中是否有一种方法来检查一个特定的列，看看该值是否出现不止一次，如果是的话，得到行

假设我的表是这样的:

id | first_name | last_name | age
1     john           doe       20
..    .......       .....     ...
..    .......       .....     ...

所以我希望mysql去并带回一个列表，其中年龄是18，其中first_name出现不止一次

SELECT y.id, y.first_name, y.last_name, y.age
    FROM (SELECT first_name
              FROM YourTable
              WHERE age = 18
              GROUP BY first_name
              HAVING COUNT(*) > 1) t
        INNER JOIN YourTable y
            ON t.first_name = y.first_name
    WHERE y.age = 18;

您可以使用JOIN:

SELECT DISTINCT T1.first_name
FROM yourtable T1
JOIN yourtable T2
ON T1.id < T2.id
AND T1.first_name = T2.first_name
AND T2.age = 18
WHERE T1.age = 18

select id, first_name, last_name, age
from YourTable where first_name in
(
    select first_name
    from YourTable 
    where age = 18
    group by first_name
    having count(first_name) > 1
)