SQL PostgreSQL - GROUP BY 子句或在聚合函数中使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10161696/
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 - GROUP BY clause or be used in an aggregate function
提问by user984621
I found some topics here on SO, but I still can't find the right setup for my query.
我在这里找到了一些关于 SO 的主题，但我仍然找不到适合我的查询的设置。
This is query, that works me well on localhost:
这是查询，在本地主机上运行良好：
@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')
But on Heroku gives me the error above - GROUP BY clause or be used in an aggregate function.
但是在 Heroku 上给了我上面的错误 - GROUP BY 子句或在聚合函数中使用。
Then I have read somewhere, that I should specify all columns in the table, so I tried this:
然后我在某处读到，我应该指定表中的所有列，所以我尝试了这个：
@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')
But this doesn't work on localhost and also not on Heroku...
但这不适用于本地主机，也不适用于 Heroku ......
What should be the right config of the query?
查询的正确配置应该是什么？
采纳答案by Arion
I think you are trying to aggregate and group by on the same column. It depends on what data you want. Ether do this:
我认为您正在尝试在同一列上进行聚合和分组。这取决于你想要什么数据。以太这样做：
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
Or you want to count all maybe? Then like this:
或者你想数一数？然后像这样：
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
回答by Erwin Brandstetter
A query such as this (retrieving all or most rows) is fasterif you GROUPbefore you JOIN. Like this:
查询像这样（检索所有或大部分行）是更快，如果你GROUP在你面前JOIN。像这样：
SELECT id, name, created_at, updated_at, u.ct
FROM cars c
LEFT JOIN (
SELECT car_id, count(*) AS ct
FROM users
GROUP BY 1
) u ON u.car_id = c.id
ORDER BY u.ct DESC;
This way you need far fewer join operations. And the rows of the table carsdo not have to be first multiplied by joining to many users each and then grouped back to be unique again.
Only the right table has to be grouped, which makes the logic simpler, too.
这样，您需要的连接操作就会少得多。并且表的行cars不必首先通过加入许多用户来相乘，然后再重新分组以再次唯一。
只需要对正确的表进行分组，这也使逻辑更简单。
回答by user673207
You can use MAX()trick on cars column.
您可以MAX()在汽车列上使用技巧。
@cars = Car.find_by_sql('
SELECT cars.id, MAX(cars.name) as name, MAX(cars.created_at) AS
created_at, MAX(cars.updated_at) as updated_at, COUNT(cars.id) AS counter
FROM cars LEFT JOIN users ON cars.id=users.car_id
GROUP BY cars.id ORDER BY counter DESC')
