订购结果表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屋!