MySQL重复顺序由高到低 [英] MySQL order by duplicates top first

问题描述

就我不是mysql专业人士而言，我有一个lammer问题

I have a lammer question, as far as I am not pro at mysql

我有类似

   id    color
   1     red
   2     green
   3     yellow
   4     green
   5     green
   6     red

我想按重复项分组，最频繁的重复项优先 因此应通过以下方式选择它:

I want to group by duplicates , and most frequent duplicate first so it should be selected this way:

   id    color
   2     green
   4     green
   5     green
   1     red
   6     red
   3     yellow

谢谢

推荐答案

"...最频繁的重复在前."

查询

SELECT  a.*
FROM    TableName a
        INNER JOIN 
        (
            SELECT  Color, COUNT(*) totalCount
            FROM    TableName
            GROUP   BY Color
        ) b ON  a.Color = b.Color
ORDER   BY b.TotalCount DESC, a.ID ASC

• SQLFiddle演示
• SQLFiddle Demo

输出

╔════╦════════╗
║ ID ║ COLOR  ║
╠════╬════════╣
║  2 ║ green  ║
║  4 ║ green  ║
║  5 ║ green  ║
║  1 ║ red    ║
║  6 ║ red    ║
║  3 ║ yellow ║
╚════╩════════╝

这篇关于MySQL重复顺序由高到低的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！