PostgreSQL GROUP BY 子句或用于聚合函数

发布于04月15日

我在这里找到了一些主题，但仍然找不到适合我的查询的设置.

这是一个在本地主机上运行良好的查询:

@cars = Car.find_by_sql('SELECT cars.*, COUNT(cars.id) AS counter 
                         FROM cars 
                         LEFT JOIN users ON cars.id=users.car_id 
                         GROUP BY cars.id ORDER BY counter DESC')

但在Heroku上，误差大于-GROUP BY clause or be used in an aggregate function.

然后我在某个地方读到，我应该指定表中的所有列，所以我try 了以下方法:

@cars = Car.find_by_sql('SELECT cars.id, cars.name, cars.created_at, 
                                cars.updated_at, COUNT(cars.id) AS counter 
                         FROM cars 
                         LEFT JOIN users ON cars.id=users.car_id 
                         GROUP BY (cars.id, cars.name, cars.created_at, cars.updated_at) 
                         ORDER BY counter DESC')

但这在本地主机和Heroku上都不起作用...

查询的正确配置应该是什么？

推荐答案

我认为你是在试图在同一列上进行聚合和分组.这取决于你想要什么数据.你可以这样做:

SELECT 
 cars.name, 
 cars.created_at, 
 cars.updated_at, 
 COUNT(cars.id) AS counter 
FROM cars 
LEFT JOIN users 
  ON cars.id=users.car_id 
GROUP BY cars.name, cars.created_at, cars.updated_at 
ORDER BY counter DESC

或者你想数一数？然后像这样:

SELECT
 cars.id,
 cars.name, 
 cars.created_at, 
 cars.updated_at, 
 COUNT(*) AS counter 
FROM cars 
LEFT JOIN users 
  ON cars.id=users.car_id 
GROUP BY cars.id, cars.name, cars.created_at, cars.updated_at 
ORDER BY counter DESC