沖縄チームのキリです。前回二番目に大きいデータに一致するレコードを取り出すクエリを書きました。業務で必要になるはずだったのですが実は前回分のデータをくっつけて取得するのが必要な処理でした。
さて以下のようなテーブルがあります。仮にテーブル名をpostsとしておきます。
| id | user_id | content |
|---|---|---|
| 1 | 1 | A |
| 2 | 1 | B |
| 3 | 1 | C |
| 4 | 2 | A |
| 5 | 3 | A |
| 6 | 3 | B |
次のような形でデータを取り出したいとします。idの降順に並べておきます。
| id | user_id | content | prev_id | prev_content |
|---|---|---|---|---|
| 6 | 3 | B | 5 | A |
| 5 | 3 | A | NULL | NULL |
| 4 | 2 | A | NULL | NULL |
| 3 | 1 | C | 2 | B |
| 2 | 1 | B | 1 | A |
| 1 | 1 | A | NULL | NULL |
例によってDEPENDENT SUBQUERYは避けたいです。さてどうしたらいいでしょうか…？まずはuser_idをキーに自己結合してみます。
SELECT posts.id, posts.user_id, posts.content, posts2.id AS post2_id,
posts2.user_id AS post2_user_id, posts2.content AS post2_content
FROM posts
INNER JOIN posts AS posts2 ON posts.user_id = posts2.user_id
ORDER BY posts.id ASC, posts2.id ASC| id | user_id | content | post2_id | post2_user_id | post2_content |
|---|---|---|---|---|---|
| 1 | 1 | A | 1 | 1 | A |
| 1 | 1 | A | 2 | 1 | B |
| 1 | 1 | A | 3 | 1 | C |
| 2 | 1 | B | 1 | 1 | A |
| 2 | 1 | B | 2 | 1 | B |
| 2 | 1 | B | 3 | 1 | C |
| 3 | 1 | C | 1 | 1 | A |
| 3 | 1 | C | 2 | 1 | B |
| 3 | 1 | C | 3 | 1 | C |
| 4 | 2 | A | 4 | 2 | A |
| 5 | 3 | A | 5 | 3 | A |
| 5 | 3 | A | 6 | 3 | B |
| 6 | 3 | B | 5 | 3 | A |
| 6 | 3 | B | 6 | 3 | B |
ユーザーのレコードに同じユーザーのレコードすべてが結合されました。ユーザーごとのレコード数が2乗になってしまうのでテスト用に用意したデータでもなければLIMIT指定しておくべきですね。次にユーザーごとにこれより古いレコードを持つレコードだけを取得したいので、結合条件に『posts.id > posts2.id』を追加します。
SELECT posts.id, posts.user_id, posts.content, posts2.id AS post2_id,
posts2.user_id AS post2_user_id, posts2.content AS post2_content
FROM posts
INNER JOIN posts AS posts2 ON posts.user_id = posts2.user_id
AND posts.id > posts2.id
ORDER BY posts.id ASC, posts2.id ASC| id | user_id | content | post2_id | post2_user_id | post2_content |
|---|---|---|---|---|---|
| 2 | 1 | B | 1 | 1 | A |
| 3 | 1 | C | 1 | 1 | A |
| 3 | 1 | C | 2 | 1 | B |
| 6 | 3 | B | 5 | 3 | A |
表を見ているとposts.idが3のレコードが2つありますが、前回分のレコードはこのうちposts2.idが最も大きいものであることに気付くと思います。MAX()関数を使って取り出します。（MySQLは5.7でGROUP BYの挙動が変更され、GROUP BYに指定したカラム、あるいは集計関数を通したカラムしかSELECTに含められません）
SELECT posts.id, MAX(posts2.id) AS prev_id
FROM posts
INNER JOIN posts AS posts2 ON posts.user_id = posts2.user_id
AND posts.id > posts2.id
GROUP BY posts.id
ORDER BY posts.id ASC| id | prev_id |
|---|---|
| 2 | 1 |
| 3 | 2 |
| 6 | 5 |
これで特定のレコードと同じユーザーの前回のレコードのidを取得することができました。あとはこのクエリをサブクエリにしてpostsに結合することで目的のデータを取り出すことができます。
SELECT posts.id, posts.user_id, posts.content, prev_posts.id AS prev_id,
prev_posts.content AS prev_content
FROM posts
LEFT JOIN (
SELECT posts.id, MAX(posts2.id) AS prev_id
FROM posts
INNER JOIN posts AS posts2 ON posts.user_id = posts2.user_id
AND posts.id > posts2.id
GROUP BY posts.id
ORDER BY posts.id ASC
) AS pivot ON posts.id = pivot.id
LEFT JOIN posts AS prev_posts ON pivot.prev_id = prev_posts.id
ORDER BY posts.id DESC
| id | user_id | content | prev_id | prev_content |
|---|---|---|---|---|
| 6 | 3 | B | 5 | A |
| 5 | 3 | A | NULL | NULL |
| 4 | 2 | A | NULL | NULL |
| 3 | 1 | C | 2 | B |
| 2 | 1 | B | 1 | A |
| 1 | 1 | A | NULL | NULL |
いやあ実に大変ですね…私がDB設計するなら素直に前回分のIDを保持するカラムを用意します。ここまで頑張ってクエリを書いたのですが、実は該当箇所に仕様変更というか削除される項目があることがあとからわかりまして、バチバチに削除項目に含まれていました🥺SQLのお勉強になったと思って前向きに受け止めておきます。