DrupalというWebフレームワークのクエリビルダーでこのような処理が行われていて個人的に目からウロコだったので共有します。
たとえばこんなテーブルがあったとして、店（shop_id）ごとの一番値段(price)高い商品を取得したい。
| shop_id | item_name | price |
|---|---|---|
| 1 | pencil | 200 |
| 1 | eraser | 180 |
| 2 | t-shirt | 1900 |
| 2 | skirt | 1500 |
| 3 | table | 20000 |
| 3 | chair | 10000 |
(テーブル名はshopsテーブルとします)
値段が欲しいだけだったら
SELECT shop_id, MAX(price) FROM shops
GROUP BY shop_id
で以下のような結果になります
| shop_id | price |
|---|---|
| 1 | 200 |
| 2 | 1900 |
| 3 | 20000 |
が、ここで商品名(item_name)も同時に取得しようとすると、標準SQLではエラーになります。
ネットを検索してみたところサブクエリを使う方法もあるようですが、今回紹介する方法では、LEFTJOINとWHEREだけで取得します。
SELECT shop_id, item_name, price FROM shops
LEFT JOIN shops AS shops2 ON shops.shop_id = shops2.shop_id AND shops.price < shops2.price
WHERE shops2.price = NULL
たったこれだけのクエリで店ごとの一番高い商品が取得できます。
| shop_id | item_name | price |
|---|---|---|
| 1 | pencil | 200 |
| 2 | t-shirt | 1900 |
| 3 | table | 20000 |
このクエリで何をしてるのかというと、まず
LEFT JOIN shops AS shops2 ON shops.shop_id = shops2.shop_id
で全く同じテーブルをshop_idを基準として右に連結しています。
この状態でクエリを実行すると
| shop_id | item_name | price | shop_id | item_name | price |
|---|---|---|---|---|---|
| 1 | pencil | 200 | 1 | pencil | 200 |
| 1 | pencil | 200 | 1 | eraser | 180 |
| 1 | eraser | 180 | 1 | pencil | 200 |
| 1 | eraser | 180 | 1 | eraser | 180 |
| 2 | t-shirt | 1900 | 2 | t-shirt | 1900 |
| 2 | t-shirt | 1900 | 2 | skirt | 1500 |
| 2 | skirt | 1500 | 2 | t-shirt | 1900 |
| 2 | skirt | 1500 | 2 | skirt | 1500 |
| 3 | table | 20000 | 3 | table | 20000 |
| 3 | table | 20000 | 3 | chair | 10000 |
| 3 | chair | 10000 | 3 | table | 20000 |
| 3 | chair | 10000 | 3 | chair | 10000 |
となりますが、
AND shops.price < shops2.price
であらかじめ右の価格が大きい場合しか連結しないようにしてるので、LEFT JOIN の結果は以下のようになります。
| shop_id | item_name | price | shop_id | item_name | price |
|---|---|---|---|---|---|
| 1 | pencil | 200 | NULL | NULL | NULL |
| 1 | eraser | 180 | 1 | pencil | 200 |
| 2 | t-shirt | 1900 | NULL | NULL | NULL |
| 2 | skirt | 1500 | 2 | t-shirt | 1900 |
| 3 | table | 20000 | NULL | NULL | NULL |
| 3 | chair | 10000 | 3 | table | 20000 |
店で一番高い商品の右側だけがNULLになるので、
WHERE shops2.price = NULL
で絞り込めば完了です。
| shop_id | item_name | price |
|---|---|---|
| 1 | pencil | 200 |
| 2 | t-shirt | 1900 |
| 3 | table | 20000 |