MySQL 从每个 GROUP BY (unique_id) 和 ORDER BY 编号中选择具有 MAX id 的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35867601/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-31 21:38:50 来源:igfitidea点击:
SELECT row with MAX id from each GROUP BY (unique_id) and ORDER BY number
提问by user3100193
I have table with id, unique_id, and order_number.
我有带有 id、unique_id 和 order_number 的表。
- I want to GROUP rows by unique_id
- I want to take row with MAX id from each group
- And last thing I want to sort that rows by order_number
- 我想按 unique_id 对行进行分组
- 我想从每个组中获取 MAX id 的行
- 最后一件事我想按 order_number 对这些行进行排序
Also I have few WHERE clauses. This is my attempt which does not work:
我也很少有 WHERE 子句。这是我的尝试，但不起作用：
SELECT MAX(id) AS id
, order_number
FROM table
WHERE final = 0
AND username = '$username'
AND active = 1
GROUP
BY unique_id
ORDER
BY order_number
回答by Giorgos Betsos
You can use your query as a subquery:
您可以将查询用作子查询：
SELECT *
FROM table
WHERE id IN (SELECT MAX(id) AS id
FROM table
WHERE final=0 AND username='$username' AND active=1
GROUP BY unique_id)
ORDER BY order_number
or, if idis not unique, use JOIN:
或者，如果id不是唯一的，请使用JOIN：
SELECT t1.*
FROM table AS t1
JOIN (SELECT MAX(id) AS max_id, unique_id
FROM table
WHERE final=0 AND username='$username' AND active=1
GROUP BY unique_id
) AS t2 ON t1.unique_id = t2.unique_id AND t1.id = t2.unique_id
ORDER BY order_number
回答by Dylan Su
Try this:
尝试这个：
SELECT id, redni_broj
FROM table
WHERE final=0 AND username='$username' AND active=1 AND
id IN (
SELECT MAX(id) FROM table table2
WHERE table.unique_id = table2.unique_id
)
GROUP BY unique_id
ORDER BY order_number;
