MySQL标记Select上具有相同值的所有记录(包括第一个) [英] MySQL Markup all records on Select with same values (first one included)

问题描述

与选择时具有重复项的MySQL标记记录连接.区别在于，我不仅要标记实际上是重复的记录，而且要标记具有相同值(包括第一个)的所有记录.

Connected with MySQL Markup Records with Duplicates on Select. The difference is I want to markup not only actually duplicated records but all records with same values including first one.

id     name 
--------------
1      John    # mark this
2      Peter 
3      John    # this
4      David 
5      John    # and this

@ m-khalid-junaid提出的方法 https://stackoverflow.com/a/47728321/1056384

The approach proposed by @m-khalid-junaid https://stackoverflow.com/a/47728321/1056384

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

它对我来说有一个或两个问题:

It has one or two problems for me:

1. 它不标记组中第一次出现的重复"记录.
2. 它使用order by id (不确定是否依赖)，但是我需要以不同的方式对真正复杂的查询中的结果进行排序.

1. It not markups first occurrence of "duplicate" record in group.
2. It uses order by id (not sure if it depends) but I need to sort result in real complex query by different ways.

如果对查询优化有意义，那么我的任务会稍微复杂一些.实际上，我只需要标记组中的重复项:

If it makes sense for query optimization, my task is a little bit more complex. Actually, I need to markup duplicates inside groups only:

id   group  name
--------------
1    1      John    # mark this (dups in group #1)
2    1      Peter 
3    1      John    # mark this (dups in group #1)
4    2      David 
5    2      John    # this is not (it's in group #2)

推荐答案

看起来像这样对我有用:

Looks like this works for me:

SELECT DISTINCT t.*,
       IF(t2.id IS NULL, 0, 1) AS is_duplicate
FROM tab t
LEFT JOIN tab t2 ON t.name = t2.name AND t.group = t2.group AND t.id <> t2.id

http://sqlfiddle.com/#!9/d24012/16/0

这篇关于MySQL标记Select上具有相同值的所有记录(包括第一个)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！