SQLで、ある特定のレコード間の比較をおこなう

規模が小さめ or システム導入が遅れがちな自社サービス系の企業に勤めている方なら頻繁にあるかと思いますが、ちょくちょく事業運営側の人たちからデータの抽出依頼を受けると思います。
そんな時に知ったSQLクエリを今回は記事として投稿しようかと思います。
※今回の題材に使用した事項は全てフィクションです

# ある日、こんな依頼が。。。
本社社員「支社の各部署が注文している備品あるじゃない？　あれの注文体系を管理したいから良い感じにデータ出してくれない？」

本社社員「なんか同じ支社でも、部署毎に違う業者に注文してたりしてなかったりするみたいなんだよね〜」

本部社員「もし同じ業者に頼んでいるなら、そこに一本化した方が良いじゃん？　それを洗い出して欲しいな〜」

私「かしこまりました。」

# データの確認
私「さて、どんなデータが入っているんだろうか。。。」

```sql
mysql> SELECT * FROM branch_offices;
+----+---------+
| id | name    |
+----+---------+
|  1 | 支社1   |
|  2 | 支社2   |
|  3 | 支社3   |
|  4 | 支社4   |
|  5 | 支社5   |
+----+---------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM departments;
+----+------------------+-----------------------+
| id | branch_office_id | name                  |
+----+------------------+-----------------------+
|  1 |                1 | 営業部                |
|  2 |                2 | 営業部                |
|  3 |                2 | 経理部                |
|  4 |                3 | 営業部                |
|  5 |                4 | 営業部                |
|  6 |                4 | 経理部                |
|  7 |                4 | 企画部                |
|  8 |                4 | 経営部                |
|  9 |                5 | 営業部                |
| 10 |                5 | コールセンター        |
| 11 |                5 | 法務部                |
+----+------------------+-----------------------+
11 rows in set (0.01 sec)

mysql> SELECT * FROM map_supplier_departments;
+----+---------------+-------------+
| id | department_id | supplier_id |
+----+---------------+-------------+
|  1 |             1 |           1 |
|  2 |             2 |           1 |
|  3 |             2 |           2 |
|  4 |             2 |           3 |
|  5 |             3 |           1 |
|  6 |             3 |           3 |
|  7 |             3 |           5 |
|  8 |             4 |           1 |
|  9 |             5 |           2 |
| 10 |             5 |           4 |
| 11 |             5 |           6 |
| 12 |             6 |           1 |
| 13 |             6 |           2 |
| 14 |             6 |           3 |
| 15 |             7 |           1 |
| 16 |             8 |           1 |
| 17 |             8 |           3 |
| 18 |             8 |           6 |
| 19 |             9 |           1 |
| 20 |            10 |           2 |
| 21 |            10 |           4 |
| 22 |            11 |           3 |
+----+---------------+-------------+
22 rows in set (0.01 sec)

```
**※suppliersはデータ用意するの面倒なので、mapテーブルを用意しただけにとどめます。**
**今回の主題はあくまでSQLクエリなのでこの辺りはご容赦を。。。**

私「なるほどね〜」

私「↓って言ってたし、まずは複数部署があるところをピックアップすればいっか」
> 本社社員「なんか同じ支社でも、部署毎に違う業者に注文してたりしてなかったりするみたいなんだよね〜」

> 本部社員「もし同じ業者に頼んでいるなら、そこに一本化した方が良いじゃん？　それを洗い出して欲しいな〜」

```sql
mysql> SELECT bo.name AS branch_office_name, d.name AS department_name, map_sd.supplier_id FROM departments AS d                                                  -> LEFT JOIN branch_offices AS bo ON bo.id = d.branch_office_id
    -> LEFT JOIN map_supplier_departments AS map_sd ON d.id = map_sd.department_id
    -> WHERE d.branch_office_id IN (
    ->     SELECT branch_office_id FROM departments
    ->     GROUP BY branch_office_id
    ->     HAVING count(branch_office_id) > 1
    -> );
+--------------------+-----------------------+-------------+
| branch_office_name | department_name       | supplier_id |
+--------------------+-----------------------+-------------+
| 支社2              | 営業部                |           1 |
| 支社2              | 営業部                |           2 |
| 支社2              | 営業部                |           3 |
| 支社2              | 経理部                |           1 |
| 支社2              | 経理部                |           3 |
| 支社2              | 経理部                |           5 |
| 支社4              | 営業部                |           2 |
| 支社4              | 営業部                |           4 |
| 支社4              | 営業部                |           6 |
| 支社4              | 経理部                |           1 |
| 支社4              | 経理部                |           2 |
| 支社4              | 経理部                |           3 |
| 支社4              | 企画部                |           1 |
| 支社4              | 経営部                |           1 |
| 支社4              | 経営部                |           3 |
| 支社4              | 経営部                |           6 |
| 支社5              | 営業部                |           1 |
| 支社5              | コールセンター        |           2 |
| 支社5              | コールセンター        |           4 |
| 支社5              | 法務部                |           3 |
+--------------------+-----------------------+-------------+
20 rows in set (0.00 sec)
```

私「とりあえず、これで複数部署のある支社は分かったけど。。。　これからどうやって**支社別で見て、異なる部署で同じ業者に頼んでいるところを抽出すれば良いんだろうか。。。**」

# 相関サブクエリを使う
私「へー、ググったら↓こんなのが出てきた！　これでイケそう！！」
https://codezine.jp/article/detail/907

```sql
mysql> SELECT bo.name AS branch_office_name, d.name AS department_name, map_sd.supplier_id FROM departments AS d
    -> LEFT JOIN branch_offices AS bo ON bo.id = d.branch_office_id
    -> LEFT JOIN map_supplier_departments AS map_sd ON d.id = map_sd.department_id
    -> WHERE d.branch_office_id IN (
    ->     SELECT branch_office_id FROM departments
    ->     GROUP BY branch_office_id
    ->     HAVING count(branch_office_id) > 1
    -> )
    -> AND map_sd.supplier_id IN (
    ->     SELECT map_sd_2.supplier_id FROM map_supplier_departments AS map_sd_2
    ->     LEFT JOIN departments AS d_2 ON d_2.id = map_sd_2.department_id
    ->     WHERE d.branch_office_id = d_2.branch_office_id
    ->     AND d.id != d_2.id
    -> )
    -> ORDER BY bo.name, map_sd.supplier_id, d.name;
+--------------------+-----------------+-------------+
| branch_office_name | department_name | supplier_id |
+--------------------+-----------------+-------------+
| 支社2              | 営業部          |           1 |
| 支社2              | 経理部          |           1 |
| 支社2              | 営業部          |           3 |
| 支社2              | 経理部          |           3 |
| 支社4              | 企画部          |           1 |
| 支社4              | 経営部          |           1 |
| 支社4              | 経理部          |           1 |
| 支社4              | 営業部          |           2 |
| 支社4              | 経理部          |           2 |
| 支社4              | 経営部          |           3 |
| 支社4              | 経理部          |           3 |
| 支社4              | 営業部          |           6 |
| 支社4              | 経営部          |           6 |
+--------------------+-----------------+-------------+
13 rows in set (0.00 sec)
```

私「これでデータ抽出完了！」

私「支社2はsupplier_id= 1, 3。　支社4はsupplier_id= 1, 2, 3, 6に注文してるみたいだ！」

私「本部社員さん〜！　これでどうでしょうか？？」

本部社員「おーありがとう！　支社4は多いな。。。　4つのうちから絞るように依頼するのは面倒だなぁ。。。」

〜終わり〜

## ちょっとした解説
今回のクエリの肝は、以下の箇所です。

```sql
AND map_sd.supplier_id IN (
    SELECT map_sd_2.supplier_id FROM map_supplier_departments AS map_sd_2
    LEFT JOIN departments AS d_2 ON d_2.id = map_sd_2.department_id
    WHERE d.branch_office_id = d_2.branch_office_id
    AND d.id != d_2.id
)
```

大元のFROM句で作成された集合とほぼ同じ集合を作成し、比較したい範囲をWHERE句で指定してあげる形となっています。
（ここでは、支社は同じだけど異なる部署間で比較となってます）
そして、その範囲内で抽出されるsupplier_idと同等のsupplier_idを保持するレコードがクエリ結果として出力される、という訳です。

いやー、SQLでこんなループみたいなことできるんですね。
初めて知りました。
まだまだ勉強していかなくてはなぁ。。。