database - postgreSQL 选择聚合函数中未使用的其他列

我正在尝试在 PostgreSQL 中编写查询，但我有点沮丧，因为它适用于其他数据库引擎。我需要从给定的连接表中选择前 5 个用户，如下所示:

SELECT users.*,
       COUNT(deals.id) AS num_deals
FROM users, deals
WHERE deals.users_id = users.id
GROUP BY users.id
ORDER BY num_deals LIMIT 5;

I need the top 5 users. This code works in sqlite, mysql, etc, yet PostgreSQL refuses to select additional fields that aren't used in aggregate functions. I'm getting the following error:

PGError: ERROR:  column "users.id" must appear in the GROUP BY clause or be used in an aggregate function

我怎样才能在 PostgreSQL 中做到这一点？

最佳答案

你可以试试:

SELECT users.*, a.num_deals FROM users, (
    SELECT deal.id as dealid, COUNT(deals.id) AS num_deals
    FROM deals
    GROUP BY deal.id
) a where users.id = a.dealid
ORDER BY a.num_deals DESC
LIMIT 5

关于database - postgreSQL 选择聚合函数中未使用的其他列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/4755518/