如何订购或选择MySQL GROUP BY子句中的行? [英] How to order or choose rows in MySQL GROUP BY clause?
问题描述
我有这样的表:
id号otherfields
--------- ----------------------------------
664 48 aaa
665 49 bbb
666 55 ccc
667 48 ddd
我的查询按<$ c $分组c> number 字段,我希望它选择第一个(最低) id
,以便数据像 ccc,aaa,bbb
(当由 number
命令时)。然而,我得到了 ccc,ddd,bbb
- 换句话说,它选择的行数为#667而不是#664。
奇怪的是,这只发生在现场服务器上;在本地主机上,即使表完全相同(从本地主机导出,导入到服务器上),我也可以正确地使用它。
是否可以确保 GROUP BY
子句选择第一个ID?
在MySQL中。你必须使用连接。
SELECT id,number,otherfields FROM table
WHERE id in(SELECT min(id)FROM table GROUP BY number )
I have a table like this:
id number otherfields
-------------------------------------------
664 48 aaa
665 49 bbb
666 55 ccc
667 48 ddd
My query groups by the number
field, and I want it to pick the first (lowest) id
, so that the data comes out like ccc,aaa,bbb
(when ordered by number
). However I'm getting ccc,ddd,bbb
- in other words, it's picking row #667 instead of #664 for the number 48.
Oddly, this only happens on the live server; on localhost I get it the correct way even though the table is exactly the same (exported from localhost, imported onto server).
Is it possible to ensure that the GROUP BY
clause picks the first ID?
No, it is not possible in MySQL. You have to use a join.
SELECT id, number, otherfields FROM table
WHERE id in (SELECT min(id) FROM table GROUP BY number)
这篇关于如何订购或选择MySQL GROUP BY子句中的行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!