postgresql错误：列必须出现在GROUPBY子句中或在聚合函数中使用

rxztt3cl 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(648)

我要从中获取“user1”的最近联系人的消息表，现在该表如下所示：

| id | receiver | sender | time       |
|----|----------|--------|------------|
| 1  | user1    | host1  | 2020-07-02 |
| 2  | host2    | user1  | 2020-07-03 |
| 3  | user3    | host3  | 2020-07-04 |

我使用mysql获得了预期的结果：

SELECT CASE WHEN receiver = 'user1' THEN sender ELSE receiver END AS id, max(time) AS time 
FROM message WHERE receiver = 'user1' OR sender = 'user1' 
GROUP BY CASE WHEN receiver = 'user1' THEN sender ELSE receiver END;

但是当我在postgres上运行这个查询时，我得到一个错误：postgresql error:column receiver必须出现在groupby子句中，或者在聚合函数中使用。有解决这个问题的办法吗？

sql postgresql node-postgres

来源：https://stackoverflow.com/questions/62899663/postgresql-error-column-must-appear-in-the-group-by-clause-or-be-used-in-an-agg

1条答案

按热度按时间

hsgswve41#

我建议 distinct on :

select distinct on ( case when receiver = 'user1' then sender else receiver end ) m.*
from messages m
where 'user1' in (receiver, sender)
order by (case when receiver = 'user1' then sender else receiver end), time desc;

赞(0）回复(0）举报 2021-07-26

我来回答

postgresql错误：列必须出现在GROUPBY子句中或在聚合函数中使用

1条答案

相关问题

热门标签

最新问答