php 如何按 DESC 顺序分组

How to group by DESC order

提问by Michael Samuel

I have the following table called questions:

我有下表称为问题：

ID | asker 
1  | Bob
2  | Bob
3  | Marley

I want to select each asker only once and if there are multiple askers with the same name, select the one of the highest id. So, the expected results:

我只想选择每个提问者一次，如果有多个同名提问者，请选择 ID 最高的一个。所以，预期的结果：

ID | asker 
3  | Marley
2  | Bob

I use the following query:

我使用以下查询：

SELECT * FROM questions GROUP by questions.asker ORDER by questions.id DESC

I get the following result:

我得到以下结果：

ID | asker 
3  | Marley
1  | Bob

So it selects the first 'Bob' it encounters instead of the last one.

所以它选择它遇到的第一个“鲍勃”而不是最后一个。

Thanks

谢谢

回答by Taryn

If you want the last idfor each asker, then you should use an aggregate function:

如果您想要id每个的最后一个asker，那么您应该使用聚合函数：

SELECT max(id) as id, 
   asker
FROM questions 
GROUP by asker 
ORDER by id DESC

The reason why you were getting the unusual result is because MySQL uses an extension to GROUP BYwhich allows items in a select list to be nonaggregated and not included in the GROUP BY clause. This however can lead to unexpected results because MySQL can choose the values that are returned. (See MySQL Extensions to GROUP BY)

你得到不寻常结果的原因是因为 MySQL 使用了一个扩展，GROUP BY它允许选择列表中的项目是非聚合的，而不是包含在 GROUP BY 子句中。然而，这可能会导致意外结果，因为 MySQL 可以选择返回的值。（请参阅GROUP BY 的 MySQL 扩展）

From the MySQL Docs:

来自 MySQL 文档：

MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. ... You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

MySQL 扩展了 GROUP BY 的使用，以便选择列表可以引用未在 GROUP BY 子句中命名的非聚合列。...您可以使用此功能通过避免不必要的列排序和分组来获得更好的性能。但是，这主要在未在 GROUP BY 中命名的每个非聚合列中的所有值对于每个组都相同时很有用。服务器可以自由地从每个组中选择任何值，因此除非它们相同，否则选择的值是不确定的。此外，添加 ORDER BY 子句不会影响从每个组中选择值。结果集的排序发生在选择值之后，并且 ORDER BY 不影响服务器选择的值。

Now if you had other columns that you need to return from the table, but don't want to add them to the GROUP BYdue to the inconsistent results that you could get, then you could use a subquery to do so. (Demo)

现在，如果您有其他需要从表中返回的列，但GROUP BY由于可能获得的结果不一致而不想将它们添加到表中，那么您可以使用子查询来执行此操作。（演示）

select 
  q.Id,
  q.asker,
  q.other -- add other columns here
from questions q
inner join
(
  -- get your values from the group by
  SELECT max(id) as id, 
    asker
  FROM questions 
  GROUP by asker 
) m
  on q.id = m.id
order by q.id desc

回答by Kapil dev

Normally MySQL allows group by ascending order records only. So we can order records before grouping.

通常 MySQL 只允许按升序对记录进行分组。所以我们可以在分组之前对记录进行排序。

SELECT *
FROM (
  SELECT *
  FROM questions
  ORDER BY id DESC
) AS questions
GROUP BY questions.asker

回答by John Woo

The records need to be grouped using GROUP BYand MAX()to get the maximum ID for every asker.

记录需要使用分组GROUP BY并MAX()获得每个asker.

SELECT  asker, MAX(ID) ID
FROM    TableName
GROUP   BY asker

• SQLFiddle Demo

• SQLFiddle 演示

OUTPUT

输出

╔════════╦════╗
║ ASKER  ║ ID ║
╠════════╬════╣
║ Bob    ║  2 ║
║ Marley ║  3 ║
╚════════╩════╝

回答by Michael L.

The others are correct about using MAX(ID) to get the results you want. If you're wondering why your query doesn't work, it's because ORDER BYhappens afterthe GROUP BY.

其他关于使用 MAX(ID) 来获得你想要的结果是正确的。如果你想知道为什么你的查询不起作用，这是因为ORDER BY发生后的GROUP BY。

回答by mikep

Im writing this answer because @Taryn's first/shorter alternative in accepted answer works only if you are exactly selecting just columns used in GROUP BY and MAX. User asking question is selecting all columns in table (he used SELECT *). So when you add another 3rd column to table, that column value in query result will be incorrect. You will get mixed values from different table rows. @Taryn's second/longer alternative (using inner join and subquery) works but query is uselessly complicated and is 5 times slowerin my use case than my simple alternative below.

我写这个答案是因为@Tayn 在接受的答案中的第一个/较短的替代方案仅在您完全选择 GROUP BY 和 MAX 中使用的列时才有效。用户提问是选择表中的所有列（他使用了 SELECT *）。因此，当您向表中添加另一个第 3 列时，查询结果中的该列值将不正确。您将从不同的表行中获得混合值。@Tayn 的第二个/更长的替代方案（使用内部联接和子查询）有效，但查询非常复杂，在我的用例中比下面的简单替代方案慢 5 倍。

---

---

Consider table questions:

考虑表questions：

id | asker 
-----------
1  | Bob
2  | Bob
3  | Marley

Query SELECT max(id) as id, asker FROM questions GROUP BY asker ORDER BY id DESCreturns expected:

查询SELECT max(id) as id, asker FROM questions GROUP BY asker ORDER BY id DESC返回预期：

id | asker 
-----------
3  | Marley
2  | Bob

---

---

Now consider another table questions:

现在考虑另一个表questions：

id | asker  | other
-------------------
1  | Bob    | 1st
2  | Bob    | 2nd
3  | Marley | 3rd

Query SELECT max(id) as id, asker, other FROM questions GROUP BY asker ORDER BY id DESCreturns unexpected:

查询SELECT max(id) as id, asker, other FROM questions GROUP BY asker ORDER BY id DESC返回意外：

id | asker  | other
-------------------
3  | Marley | 3rd
2  | Bob    | 1st

... note that value of otherfor second row of result is incorrect because id=2comes from second row of table but other=1stcomes from first row of table! That is way many users in comments of Taryn's answer reports that this solution does not work.

...请注意，other结果第二行的值不正确，因为id=2来自表的第二行但other=1st来自表的第一行！许多用户在 Taryn 的回答评论中报告说该解决方案不起作用。

---

---

Possible simple solution when selecting also another columns is to use GROUP BY+ DESC:

选择另一列时可能的简单解决方案是使用GROUP BY+ DESC：

SELECT id, asker, other FROM questions GROUP BY asker DESC

SELECT id, asker, other FROM questions GROUP BY asker DESC

id | asker  | other
-------------------
3  | Marley | 3rd
2  | Bob    | 2nd

(see demo: https://www.db-fiddle.com/f/esww483qFQXbXzJmkHZ8VT/10)

（见演示：https: //www.db-fiddle.com/f/esww483qFQXbXzJmkHZ8VT/10）

... but this simple solution has some limitations:

...但这个简单的解决方案有一些限制：

• Table must be InnoDB (I think it is not problem because you will get better performance and also since MySQL >= 5.5.5 default/preferred storage engine was changed from MyISAM to InnoDB)
• You have to create index for column which is used in GROUP BY - so askerin this case (I think it is not problem because you will get better performance since index is suitable in this case. GROUP BY usually needs creation of tmp table but when index is available tmp table will not be created which is faster)
• For MySQL 5.7 and 8.0 is needed to disable SQL mode ONLY_FULL_GROUP_BY (e.g SET SESSION sql_mode = '';) or use ANY_VALUE()on selected columns which are not aggregated to avoid error ER_WRONG_FIELD_WITH_GROUP.
• Unfortunately MySQL developers removed support of ASC/DESC withing GROUP BY since MySQL 8.0 https://dev.mysql.com/worklog/task/?id=8693but fortunately there is alternative GROUP BY col1 ORDER BY col1 ASC/DESC:

• 表必须是 InnoDB（我认为这不是问题，因为您将获得更好的性能，而且因为 MySQL >= 5.5.5 默认/首选存储引擎已从 MyISAM 更改为 InnoDB）
• 您必须为 GROUP BY 中使用的列创建索引 - 所以asker在这种情况下（我认为这不是问题，因为您将获得更好的性能，因为索引适用于这种情况。GROUP BY 通常需要创建 tmp 表，但是当索引可用 tmp 表不会被创建，这是更快）
• 对于 MySQL 5.7 和 8.0，需要禁用 SQL 模式 ONLY_FULL_GROUP_BY（例如SET SESSION sql_mode = '';）或ANY_VALUE()在未聚合的选定列上使用以避免错误 ER_WRONG_FIELD_WITH_GROUP。
• 不幸的是，自 MySQL 8.0 https://dev.mysql.com/worklog/task/?id=8693以来，MySQL 开发人员使用 GROUP BY 删除了对 ASC/DESC 的支持，但幸运的是还有替代方法GROUP BY col1 ORDER BY col1 ASC/DESC：

SELECT id, asker, other FROM questions GROUP BY asker ORDER BY asker DESC

SELECT id, asker, other FROM questions GROUP BY asker ORDER BY asker DESC

id | asker  | other
-------------------
3  | Marley | 3rd
2  | Bob    | 2nd

(see demo: https://www.db-fiddle.com/f/esww483qFQXbXzJmkHZ8VT/11)

（见演示：https: //www.db-fiddle.com/f/esww483qFQXbXzJmkHZ8VT/11）

... result is the same as above with GROUP BY ... DESC(do not forget to use InnoDB and create index).

...结果与上面相同GROUP BY ... DESC（不要忘记使用 InnoDB 并创建索引）。

回答by evilReiko

It's because ORDER BYis performed AFTERGROUP BY.

这是因为ORDER BY是在AFTER 之后执行的GROUP BY。

Try this:

尝试这个：

SELECT * FROM questions
WHERE id IN 
(
    SELECT max(id) as id
    FROM questions 
    GROUP by asker 
    ORDER by id DESC
)

回答by Olimjon

To get every column:

要获取每一列：

SELECT * FROM questions
WHERE id IN 
(SELECT max(id) as id, asker
FROM questions 
GROUP by asker 
ORDER by id DESC)

Improved version of the answer of @bluefeet.

@bluefeet 答案的改进版本。