mysql - 从每个 GROUP BY (unique_id) 和 ORDER BY 数字中选择具有 MAX id 的行

Question

我有一个包含 id、unique_id 和 order_number 的表。

1. 我想按 unique_id 对行进行分组
2. 我想从每个组中取出具有 MAX id 的行
3. 最后我想按 order_number 对行进行排序

我还有几个 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

Answer 1

您可以将查询用作子查询:

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

或者，如果 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