Check with the example of checking duplicate data.
** users table **
| id | name | address |
|---|---|---|
| 1 | sasaki | tokyo |
| 2 | itou | fukuoka |
| 3 | fujita | nagasaki |
| 4 | sasaki | osaka |
| 5 | itou | nagano |
For example, suppose you have a table like the one above. I want to retrieve records with duplicate names and check the address.
sql1: Select one with duplicate name
SELECT name FROM users GROUP BY name HAVING count(name) > 1
result
| name |
|---|
| itou |
| sasaki |
In this case, we do not know the address of each, so based on this result, execute SQL as follows.
sql2: Based on duplicate results
SELECT * FROM users WHERE name IN ('itou', 'sasaki') ORDER BY name
result
| id | name | address |
|---|---|---|
| 2 | itou | fukuoka |
| 5 | itou | nagano |
| 1 | sasaki | tokyo |
| 4 | sasaki | osaka |
As shown above, each address can also be obtained.
So sql1 and sql2 are combined into a subquery.
sql3: Subquery (sql1,sql2）
SELECT * FROM users WHERE name IN (
SELECT name FROM users GROUP BY name HAVING count(name) > 1
)
ORDER BY name
Can be written as
Any controller
duplicates = User.select(:name).group(:name).having("count(name) > 1")
@users = User.where(name: duplicates)
SQL to be executed
SQL executed by Rails
SELECT "users".* FROM "users" WHERE "users"."name" IN (
SELECT "users"."name" FROM "users" GROUP BY "users"."name"
HAVING (count(name) > 1)
)
Surprisingly, only one SQL is issued.
Reference (quite easy to understand!) Handling subqueries with the IN operator of ActiveRecord (Oakbow)
Recommended Posts