我有这样的表：
id号otherfields --------- ---------------------------------- 664 48 aaa 665 49 bbb 666 55 ccc 667 48 ddd我的查询按 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 dddMy 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子句中的行？
发布评论