SQL GROUP BY 和 COUNT 使用 ActiveRecord
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31879150/
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
GROUP BY and COUNT using ActiveRecord
提问by Axil
Refering to this: Is there any difference between GROUP BY and DISTINCT
参考这个： GROUP BY 和 DISTINCT 有什么区别
Given a table that looks like this:
name
------
barry
dave
bill
dave
dave
barry
john
This query:
SELECT name, count(*) AS count FROM table GROUP BY name;
Will produce output like this:
name count
-------------
barry 2
dave 3
bill 1
john 1
What is the correct Rails convention here for ActiveModel to perform a GROUP BY with COUNT?
ActiveModel 使用 COUNT 执行 GROUP BY 的正确 Rails 约定是什么？
回答by thedanotto
Distinctand Group Byare going to give you different results. To get the results you expect you'll want to use
Distinct并且Group By会给你不同的结果。要获得您希望使用的结果
Person.group(:name).count
(1.2ms) SELECT COUNT(*) AS count_all, name AS name FROM "people" GROUP BY "people"."name"
=> {"Dan"=>3, "Dave"=>2, "Vic"=>1}
Seen above, group will return things as a hash. While distinct just returns the number of people in total, seen below.
如上所示， group 会将事物作为哈希返回。虽然distinct 只返回总人数，见下图。
Person.distinct(:name).count
(0.4ms) SELECT DISTINCT COUNT(DISTINCT "people"."id") FROM "people"
=> 6
回答by Donato
Note that the accepted answer will return a hash:
请注意，接受的答案将返回一个哈希值：
Tagging.joins(:tag).group(:name).size
(0.4ms) SELECT COUNT(*) AS count_all, `name` AS name FROM `taggings` INNER JOIN `tags` ON `tags`.`id` = `taggings`.`tag_id` GROUP BY `name`
=> {"applesauce"=>1, "oranges"=>2}
But what if you want to return the count plus some columns from different tables in a join. Then you also need to use the select ActiveRecord query:
但是如果你想在一个连接中返回计数加上来自不同表的一些列怎么办。然后你还需要使用 select ActiveRecord 查询：
collection = Tagging.select('COUNT(*) as total, taggings.taggable_id, taggings.taggable_type').joins(:tag).where(taggable_type: 'LineItem', taggable_id: ['5cad0dcc3ed1496086000031', '5cad0dcd3ed1496086000081'] ).group(:name)
collection.each do |item|
puts item.taggable_id
puts item.total
end
5cad0dcc3ed1496086000031
1
5cad0dcc3ed1496086000031
2
With this second approach, you can fetch additional details about a join relationship without any additional queries or loop constructs.
使用第二种方法，您可以获取有关连接关系的其他详细信息，而无需任何其他查询或循环构造。
