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

I'm trying to write a query in PostgreSQL and I'm getting a little frustrated because it works in other database engines. I need to select the top 5 users from a given joins table like this:

我正在尝试在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:

我需要前5名用户。此代码适用于sqlite,mysql等,但PostgreSQL拒绝选择未在聚合函数中使用的其他字段。我收到以下错误:

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

How can I do this in PostgreSQL??

我怎么能在PostgreSQL中做到这一点?

4 个解决方案

#1

You could try:

你可以尝试:

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

#2

Assuming that users.id IS a PK, then you can either

假设users.id是PK,那么你也可以

wait for 9.1

等9.1

group by all fields

按所有领域分组

use an aggregate (i.e. max() ) on all fields

在所有字段上使用聚合(即max())

#3

One other solution that works is to use all attributes implicitly in GROUP BY

另一个有效的解决方案是在GROUP BY中隐式使用所有属性

Thus following will be final query

因此,以下将是最终查询

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

If you are using framework like rails then you can implement this easily with Model.column_names function.

如果您使用的是类似rails的框架,那么您可以使用Model.column_names函数轻松实现此功能。

#4

Just in case of somebody wants ANSI-92 standard solution and doesn't like 'Oracle' way to join tables...

如果有人想要ANSI-92标准解决方案并且不喜欢'Oracle'加入表的方式......

SELECT users.*, num_deals
FROM users
JOIN
  (SELECT deals.users_id as users_id, count(deals.users_id) as num_deals
   FROM deals
   GROUP BY deals.id) grouped_user_deals
ON grouped_user_deals.users_id = users.id
ORDER BY num_deals DESC
LIMIT 5;

#1