PostgreSQL – GROUP BY子句或在聚合函数中使用
发布时间:2020-12-13 16:43:15 所属栏目:百科 来源:网络整理
导读:我在这里找到一些主题的SO,但我仍然找不到正确的设置为我的查询。 这是查询,这对我很好地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 c
我在这里找到一些主题的SO,但我仍然找不到正确的设置为我的查询。
这是查询,这对我很好地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') 但在Heroku上给我上面的错误 – GROUP BY子句或在聚合函数中使用。 然后我在某处读,我应该指定表中的所有列,所以我试过这样: @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.updated_at) ORDER BY counter DESC') 但这不工作在localhost也不在Heroku … 什么应该是正确的查询配置?
我想你正在尝试聚合和分组在同一列。这取决于你想要什么数据。以这样做:
SELECT cars.name,COUNT(cars.id) AS counter FROM cars LEFT JOIN users ON cars.id=users.car_id GROUP BY cars.name,cars.updated_at ORDER BY counter DESC 或者你想计数所有也许?然后像这样: SELECT cars.id,COUNT(*) AS counter FROM cars LEFT JOIN users ON cars.id=users.car_id GROUP BY cars.id,cars.updated_at ORDER BY counter DESC (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |