mysql 选择重复记录,选择时重复的MySQL标记记录

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

And I want something like that:

SELECT

id,

name,

-- IF(~duplicate in name~, 1, 0) AS is_duplicate

FROM tab

解决方案

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