选择时具有重复项的MySQL标记记录 [英] MySQL Markup Records with Duplicates on Select

问题描述

我已经看到 MySQL Mark Records With Duplicates 的问题和答案.但是我有不同的情况.

I have seen MySQL Mark Records with Duplicates question and answers. But I have a different situation.

如何选择所有记录，但对在给定字段中重复的记录有一个标记.

How to select all records, but have a flag for those records who have duplicates in a given field.

id     name 
--------------
1      John 
2      Peter 
3      John 
4      David 

我想要这样的东西:

SELECT 
    id,
    name,
  -- IF(~duplicate in name~, 1, 0) AS is_duplicate
FROM tab

推荐答案

我假设id设置为自动递增，那么您可以使用以下查询获得所需的输出

I assume id is set to auto increment then you can use following query for your desired output

select distinct a.*,
case when b.id is null then 0 else 1 end `duplicate`
from tab a
left join tab b 
on a.name = b.name
and a.id > b.id
order by a.id

演示

这篇关于选择时具有重复项的MySQL标记记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！