PostgreSQL 不允许我按顺序对列进行分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10342247/
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
PostgreSQL does not allow me to group a column with order
提问by flower58
In PostgreSQL i want to fetch every users at once and order them by date.
在 PostgreSQL 中，我想一次获取每个用户并按日期对其进行排序。
This is my query:
这是我的查询：
SELECT id, useridx, isread, message, date
FROM messages
WHERE isread = 1
GROUP BY useridx
ORDER BY date DESC
This is a sample data:
这是一个示例数据：
------------------------------------------------------
+ id | useridx | isread | messsage | date +
------------------------------------------------------
1 | 1 | 0 | Hello | 2012-01-01
2 | 2 | 1 | Hi | 2012-01-02
3 | 3 | 1 | Test | 2012-01-03
4 | 3 | 0 | My Msg | 2012-01-04
5 | 4 | 1 | sadasd | 2012-01-05
6 | 4 | 1 | sdfsdfd | 2012-01-06
7 | 4 | 0 | sdfsdfsd | 2012-01-07
8 | 5 | 0 | 5345634 | 2012-01-08
9 | 6 | 0 | sdfdfsd | 2012-01-09
10 | 7 | 0 | sdfsdfsf | 2012-01-10
------------------------------------------------------
Now, what i want to do is fetch this table by grouping them via useridx and order by date.
现在，我想要做的是通过 useridx 将它们分组并按日期排序来获取该表。
Expected Result:
预期结果：
------------------------------------------------------
+ id | useridx | isread | messsage | date +
------------------------------------------------------
6 | 4 | 1 | sdfsdfd | 2012-01-06
3 | 3 | 1 | Test | 2012-01-03
2 | 2 | 1 | Hi | 2012-01-02
------------------------------------------------------
Actual Result
实际结果
ERROR: column "messages.date" must appear in the GROUP BY clause or be used in an aggregate function
I do not want to group date either. I just want to group with useridx and sort them by date DESC.
我也不想分组约会。我只想用 useridx 分组并按日期 DESC 对它们进行排序。
Any help/idea is appreciated!
任何帮助/想法表示赞赏！
Note: I also tried Distinct. Not fit my needs or i did wrongly.
注意：我也尝试过 Distinct。不符合我的需要或我做错了。
I am very confused and stuckbetween DISTINCT ONand rank()methods.
我很困惑，卡在DISTINCT ON和rank()方法之间。
Conclusion: For who get the same problem here can read this as an answer. Both @kgrittn's and @mu is too short's answers are correct. I will continue to use both answers and schemas on my project and in time i can understand which one is the best -i guess-. So, pick one of them and continue to your work. You will be just fine.
结论：对于在这里遇到同样问题的人，可以将其作为答案阅读。@kgrittn 和 @mu 都太短的答案是正确的。我将继续在我的项目中使用答案和模式，随着时间的推移，我可以理解哪一个是最好的——我猜——。因此，选择其中之一并继续您的工作。你会没事的。
Last Update: Sometimes, Distinct On excludes some ids from result. Lets say i have a id column and i have 6 rows which is same. So, distinct on exlude it from the result BUT rank() just result it. So, use rank()!
上次更新：有时， Distinct On 会从结果中排除一些 id。假设我有一个 id 列，我有 6 行是相同的。因此，从结果中排除它是不同的，但 rank() 只是结果它。所以，使用 rank()！
采纳答案by mu is too short
You want to use the rank()window functionto order the results within each useridxgroup and then peel off the first one by wrapping the ranked results in a derived table:
您想使用rank()窗口函数对每个useridx组中的结果进行排序，然后通过将排名结果包装在派生表中来剥离第一个：
select id, useridx, isread, message, date
from (
select id, useridx, isread, message, date,
rank() over (partition by useridx order by date desc) as r
from messages
where isread = 1
) as dt
where r = 1
That will give your the rows with id2, 3, and 6 from your sample. You might want to add a secondary sort key in the overto consistently make a choice when you have multiple messages per useridxon the same date.
这将为id您提供样本中的 2、3 和 6行。您可能希望在 中添加辅助排序键，over以便useridx在同一日期有多个消息时始终做出选择。
You'll need at least PostgreSQL 8.4 (AFAIK) to have window functions.
您至少需要 PostgreSQL 8.4 (AFAIK) 才能拥有窗口函数。
回答by ilanco
PostgreSQL, unlike MySQL, does not show random data for columns which are not aggregated in an aggregated query.
与 MySQL 不同，PostgreSQL 不会为未在聚合查询中聚合的列显示随机数据。
The solution is in the error message
解决方法在错误信息中
ERROR: column "messages.date" must appear in the GROUP BY clause or be used in an aggregate function
Which means you must GROUP BY the "messages.date" column or use an aggregate function like MIN() or MAX() when selection this column
这意味着您必须按“messages.date”列进行分组，或者在选择此列时使用 MIN() 或 MAX() 等聚合函数
Example:
例子：
SELECT MIN(id), useridx, isread, message, MAX(date)
FROM messages WHERE isread = 1
GROUP BY useridx, isread, message
ORDER BY MAX(date) DESC
回答by kgrittn
Another option is to use SELECT DISTINCT ON(which is very different from a simple SELECT DISTINCT):
另一种选择是使用SELECT DISTINCT ON（这与 simple 非常不同SELECT DISTINCT）：
SELECT *
FROM (SELECT DISTINCT ON (useridx)
id, useridx, isread, message, date
FROM messages
WHERE isread = 1
ORDER BY useridx, date DESC) x
ORDER BY date DESC;
In some cases this can scale better than the other approaches.
在某些情况下，这可以比其他方法更好地扩展。
回答by sinhix
Years later, but can't you just order in the FROM subquery:
多年后，但您不能只在 FROM 子查询中订购：
SELECT m.id, m.useridx, m.isread, m.message, m.date
FROM (
SELECT m2.id, m2.useridx, m2.isread, m2.message, m2.date
FROM message m2
ORDER BY m2.id ASC, m2.date DESC
) m
WHERE isread = 1
GROUP BY useridx
This works for me in PostgreSQL 9.2
这在 PostgreSQL 9.2 中对我有用
回答by vyegorov
You are aggregating results.
您正在汇总结果。
This means that instead of 2 rows for user 3you will have just one row. But you also select id, message, isreadcolumns for the aggregated row. How PostgreSQL is supposed to deliver this data? Should it be max()of possible values? Maybe min()?
这意味着用户3将只有一行，而不是 2 行。但您还为聚合行选择 id, message,isread列。PostgreSQL 应该如何传送这些数据？它应该max()是可能的值吗？也许min()？
I assume, that you'd like to have the data on the newest messages. Try this query:
我假设您希望获得有关最新消息的数据。试试这个查询：
SELECT id, useridx, isread, message, date FROM messages
WHERE isread = 1 AND (useridx, date) IN
(SELECT useridx, max(date) FROM messages WHERE isread = 1 GROUP BY useridx);
