订购结果表Mysql group by [英] Order result form Mysql group by

问题描述

好吧，我有这张桌子

+-----+-----------------------------+-------------+-------------+----------+---------+
| id  | title                       | created     | updated     | category | content |
+-----+-----------------------------+---------------------+---------------------+----+
| 423 | What If I Get Sick and Die? | 2008-12-30  | 2009-03-11  | angst    | NULL    |
| 524 | Uncle Karl and the Gasoline | 2009-02-28  | NULL        | humor    | NULL    |
| 537 | Be Nice to Everybody        | 2009-03-02  | NULL        | advice   | NULL    |
| 573 | Hello Statue                | 2009-03-17  | NULL        | humor    | NULL    |
| 598 | The Size of Our Galaxy      | 2009-04-03  | NULL        | science  | NULL    |
+-----+-----------------------------+---------------------+---------------------+----+

我尝试为每个类别获取唯一的行，因此我查询类似的内容:

I try to get unique row for each category, so i query fo something like:

SELECT id,title,category FROM `entries` group by category

结果是:

+-----+-----------------------------+----------+
| id  | title                       | category |
+-----+-----------------------------+----------+
| 537 | Be Nice to Everybody        | advice   |
| 423 | What If I Get Sick and Die? | angst    |
| 524 | Uncle Karl and the Gasoline | humor    |
| 598 | The Size of Our Galaxy      | science  |
+-----+-----------------------------+----------+

结果看起来不错，但是我怎么能获得类别=幽默和id = 573的行呢?

The result looks fine, but how can i get row with category = humor and id = 573 instead?

如果我查询类似的内容

SELECT id,title,category, max(id) FROM `entries` group by category

我得到这样的结果:

+-----+-----------------------------+----------+---------+
| id  | title                       | category | max(id) |
+-----+-----------------------------+----------+---------+
| 537 | Be Nice to Everybody        | advice   |     537 |
| 423 | What If I Get Sick and Die? | angst    |     423 |
| 524 | Uncle Karl and the Gasoline | humor    |     573 |
| 598 | The Size of Our Galaxy      | science  |     598 |
+-----+-----------------------------+----------+---------+

显然那不是我想要的，任何帮助将不胜感激

Obviously thats not what i want, any help would be appreciated

我想知道每个组的mysql折叠行到底是什么? mysql是否只为每个组排第一行?

And i want to know how exactly mysql collapse row for each group? Did mysql simply take first row for each group?

推荐答案

• 使用JOIN
• Using JOIN

和带有GROUP BY的查询以获取每个类别的最大ID (我想您确实希望每个类别的行的ID都最大，不是吗?)

and a query with GROUP BY to get maximum id for every category (I guess you do want the row with maximum id for every category, don't you?)

SELECT e.id
     , e.title
     , e.created
     , e.updated
     , e.category
     , e.content
FROM entries e
JOIN
    ( SELECT max(id) AS maxid
      FROM entries
      GROUP BY category
    ) AS cat
ON e.id = cat.maxid

• 使用IN
• Using IN

和查询以获取每个类别的最大ID

and a query to get maximum id for every category

SELECT id
     , title
     , created
     , updated
     , category
     , content
FROM entries
WHERE id IN
    ( SELECT max(id)
      FROM entries
      GROUP BY category
    )

• 使用ANY
• Using ANY

和相关的子查询

SELECT e.id
     , e.title
     , e.created
     , e.updated
     , e.category
     , e.content
FROM entries e
WHERE e.id >= ANY
    ( SELECT cat.id
      FROM entries cat
      WHERE e.category = cat.category
    )

• 使用不存在
• Using NOT EXISTS

和相关的子查询

SELECT e.id
     , e.title
     , e.created
     , e.updated
     , e.category
     , e.content
FROM entries e
WHERE NOT EXISTS
    ( SELECT 1
      FROM entries cat
      WHERE cat.id > e.id
        AND e.category = cat.category
    )

这篇关于订购结果表Mysql group by的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！