通过sql语句使用rails中的临时表
我试图找到一种方法来删除数据库表中的所有重复条目。因为在删除这些重复项之前我必须对它们进行一些进一步的计算，所以我必须将它们放入临时表中。
检索重复项是一个相当复杂的sql语句，我不想以active_record方式执行...
SELECT bad_rows. *
FROM settings AS bad_rows
INNER JOIN (
SELECT user_id, record_id, MIN( id ) AS min_id
FROM settings
GROUP BY user_id, record_id
HAVING COUNT( * ) >1
) AS good_rows
ON good_rows.user_id = bad_rows.user_id
AND good_rows.record_id = bad_rows.record_id
AND good_rows.min_id <> bad_rows.id
我如何用这个语句生成这个临时表？使用 ar-extensions 来完成这项任务有意义吗？
Im trying to find a way to delete all duplicated entries in a table of my db. Cause I have to make some further calculations with this duplicates before I delete them, I will have to put them in a temporary table.
Retrieving of the duplicates is a rather complex sql statement, which I rather don't want do execute in active_record manner...
SELECT bad_rows. *
FROM settings AS bad_rows
INNER JOIN (
SELECT user_id, record_id, MIN( id ) AS min_id
FROM settings
GROUP BY user_id, record_id
HAVING COUNT( * ) >1
) AS good_rows
ON good_rows.user_id = bad_rows.user_id
AND good_rows.record_id = bad_rows.record_id
AND good_rows.min_id <> bad_rows.id
How can I generate this temporary table with this statement? Does it make sense to use ar-extentions for this task?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我找到了这个链接：http://www.ruby-forum.com/topic/55322
其他人也有类似的问题。他们基本上建议直接通过AR的execute()方法，或者使用迁移风格的代码（例如create_table :my_table等）。
I found this link: http://www.ruby-forum.com/topic/55322
Where someone else had a similar problem. They basically recommended going straight through AR's execute() method, or using migration style code (e.g create_table :my_table etc).