使用 Active Record、Rails & 查找具有多个重复字段的行Postgres [英] Find rows with multiple duplicate fields with Active Record, Rails & Postgres
问题描述
使用 Postgres 和 Activerecord 在多列中查找具有重复值的记录的最佳方法是什么?
What is the best way to find records with duplicate values across multiple columns using Postgres, and Activerecord?
我在这里找到了这个解决方案:
I found this solution here:
User.find(:all, :group => [:first, :email], :have => "count(*) > 1" )
但它似乎不适用于 postgres.我收到此错误:
But it doesn't seem to work with postgres. I'm getting this error:
PG::GroupingError: ERROR: column "parts.id" 必须出现在 GROUP BY 子句中或用于聚合函数中
PG::GroupingError: ERROR: column "parts.id" must appear in the GROUP BY clause or be used in an aggregate function
推荐答案
已测试 &工作版本
User.select(:first,:email).group(:first,:email).having("count(*) > 1")
此外，这有点无关但很方便.如果您想查看找到每个组合的次数，请将 .size 放在末尾:
Also, this is a little unrelated but handy. If you want to see how times each combination was found, put .size at the end:
User.select(:first,:email).group(:first,:email).having("count(*) > 1").size
你会得到一个看起来像这样的结果集:
and you'll get a result set back that looks like this:
{[nil, nil]=>512,
["Joe", "test@test.com"]=>23,
["Jim", "email2@gmail.com"]=>36,
["John", "email3@gmail.com"]=>21}
觉得这很酷，以前没见过.
Thought that was pretty cool and hadn't seen it before.
感谢 Taryn，这只是她回答的调整版本.
Credit to Taryn, this is just a tweaked version of her answer.
这篇关于使用 Active Record、Rails & 查找具有多个重复字段的行Postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
