【SQL】縦方向に保存されている進捗の様なレコードを条件づけて抽出する方法
業務によくある「申請」のようなテーブルレコードと、その申請に対し複数の決裁者が「未処理」「承認」「否決」といったステータスを埋めるというようなテーブルレコードがあった際、現状のステータスのレコードを検索するSQLをどうやって書くのか悩んだので、私なりに考えた方法を備忘録としてポストしておきたいと思います。
先に方法を書いておくと、以前ポストした内容でもある「GROUP_CONCAT」を使って実現させました。
1.テーブル作成
申請マスタテーブル
「出張先」と「出張する人」を登録するテーブルを作成。
実際には期間や目的など色々と持たせるべき項目はあると思いますが、サンプルなので最低限のカラム構成とします。
|
1 2 3 4 5 6 |
CREATE TABLE `businesstrips` ( `id` INT UNSIGNED AUTO_INCREMENT, `place` VARCHAR(255) NOT NULL, `employee_name` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB DEFAULT CHARSET=utf8; |
レコードを登録します。
|
1 2 3 4 5 6 7 8 |
INSERT INTO `businesstrips` (`id`, `place`, `employee_name`) VALUES (null, '大阪', '社員A'), (null, '大阪', '社員A'), (null, '札幌', '社員B'), (null, '沖縄', '社員C'), (null, '福岡', '社員C'), (null, '福岡', '社員C'), (null, '札幌', '社員A'); |
決裁テーブル
先程の申請マスタを外部キーに持つ「決裁」用テーブルを作成。
ステータスは「0:未処理」「1:承認」「2:否決」となるようにします。
|
1 2 3 4 5 6 7 |
CREATE TABLE `businesstrip_results` ( `id` INT UNSIGNED AUTO_INCREMENT, `businesstrip_id` INT NOT NULL, -- 申請レコード外部キー `status` VARCHAR(4) NOT NULL DEFAULT '0', -- （0:未処理 / 1:承認 / 2:否決） `approval_employee_name` VARCHAR(255) NOT NULL, -- 決裁者 PRIMARY KEY (`id`) ) ENGINE = InnoDB DEFAULT CHARSET=utf8; |
以下のレコードを作成します。
案件としては7つ。それぞれ縦方向のレコードとして決裁ステータスと決裁者を設定します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
INSERT INTO `businesstrip_results` (`id`, `businesstrip_id`, `status`, `approval_employee_name`) VALUES -- （社員A）大阪行き (null, '1', '1', '決裁者A'), (null, '1', '1', '決裁者B'), (null, '1', '1', '決裁者C'), -- （社員A）大阪行き (null, '2', '1', '決裁者A'), (null, '2', '1', '決裁者B'), (null, '2', '1', '決裁者C'), -- （社員B）札幌行き (null, '3', '1', '決裁者D'), (null, '3', '0', '決裁者E'), -- 未処理 -- （社員C）沖縄行き (null, '4', '2', '決裁者A'), -- 否決 (null, '4', '0', '決裁者D'), (null, '4', '0', '決裁者E'), -- （社員C）福岡行き (null, '5', '1', '決裁者A'), (null, '5', '1', '決裁者D'), (null, '5', '2', '決裁者E'), -- 否決 -- （社員C）福岡行き (null, '6', '1', '決裁者A'), (null, '6', '1', '決裁者D'), (null, '6', '1', '決裁者E'), -- （社員A）札幌行き (null, '7', '1', '決裁者A'), (null, '7', '2', '決裁者B'), -- 否決 (null, '7', '0', '決裁者C'); |
要約すると
- 純粋に未処理な案件：3
- 否決された案件：4、5、7
- 承認された案件：1、2、6
となっています。
「否決」された後のレコードには未処理のステータスが登録されたままとなります。
2.抽出していく
2-1.全て出力
|
1 2 3 4 5 |
SELECT t.id, t.employee_name, t.place, r.* FROM `businesstrip_results` AS r LEFT JOIN `businesstrips` AS t ON t.id = r.businesstrip_id; |
単純にLEFT JOINして表示。20レコードが並んでいます。
2-2.GROUP_CONCATでステータス値を文字列としてstsカラムに結合する
決裁テーブルのbusinesstrip_id（申請マスタの外部キー）を GROUP_CONCAT でグループ化しながら、ステータスを「sts」カラムに文字列として結合した結果を表示します。
|
1 2 3 4 5 6 |
SELECT t.id, t.employee_name, t.place, GROUP_CONCAT(r.status SEPARATOR '') AS `sts` FROM `businesstrip_results` AS r LEFT JOIN `businesstrips` AS t ON t.id = r.businesstrip_id GROUP BY r.businesstrip_id; |
結果
案件別に7レコードが表示され、縦方向にあった決裁ステータスが文字列で結合されました。GROUP_CONCATさまさまです。
再度整理しておくと、
stsが「全て1」で構成されているのが、承認済みの案件。
「2」を**含む**のが否決された案件。
「0」を含む案件は「2」の有無で純粋な「処理待ち」か、最終決裁者までに否決された案件となります。
ここまで来るとあとは何とかなりそうな予感がしますが、ステップアップしながら解説します。
2-3.「1」のみレコード（承認済）を抽出
全て承認されたレコードを抽出してみます。
条件としては「全て1」なので、裏返して「0も2も含まない」としてSQL化します。
GROUP BY しているので HAVING 句でフィルタします。
|
1 2 3 4 5 6 7 |
SELECT r2.businesstrip_id, GROUP_CONCAT(r2.status SEPARATOR '') AS `sts` FROM `businesstrip_results` AS r2 GROUP BY r2.businesstrip_id HAVING `sts` NOT LIKE '%0%' AND `sts` NOT LIKE '%2%'; |
結果
↑ 想定通り、案件1、案件2、案件6が抽出されました。
否決された案件を抽出する場合は、HAVINGの内容を以下のようにします。
|
1 |
HAVING `sts` LIKE '%2%' -- 否決されたもの（2を含むもの） |
↑ 想定通り、案件4、案件5、案件7が抽出されました。
同様に、処理待ちのもの（2を含まず、0を含むもの）は以下のようにします。
|
1 |
HAVING `sts` LIKE '%0%' AND `sts` NOT LIKE '%2%' -- 処理待ちのみ（2を含まず、0を含むもの） |
↑ 想定通り、案件3のみが抽出されました。
2-4.(2-3)の結果から「businesstrip_id」のみを抽出
先程の結果から businesstrip_id カラムのみを抽出してみます。
ここがなかなか悩むところでしたが、SELECTした結果を別名のテーブル（テーブル名：r2）となるようサブクエリにしてSQL化します。
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT a.businesstrip_id FROM ( SELECT r2.businesstrip_id, GROUP_CONCAT(r2.status SEPARATOR '') AS `sts` FROM `businesstrip_results` AS r2 GROUP BY r2.businesstrip_id HAVING `sts` NOT LIKE '%0%' AND `sts` NOT LIKE '%2%' ) AS `a`; |
結果
ばっちり取り出せました。
2-5.最終形
では、これまでのステップを踏まえたSQLでほしいレコード（案件番号、出張する人、場所）を抽出してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT t.id, t.employee_name, t.place FROM `businesstrips` AS t WHERE t.id IN ( SELECT a.businesstrip_id FROM ( SELECT r2.businesstrip_id, GROUP_CONCAT(r2.status SEPARATOR '') AS `sts` FROM `businesstrip_results` AS r2 GROUP BY r2.businesstrip_id HAVING `sts` NOT LIKE '%0%' AND `sts` NOT LIKE '%2%' -- 最後まで承認済み（１のみ） -- HAVING `sts` LIKE '%2%' -- 否決されたもの（2を含むもの） -- HAVING `sts` LIKE '%0%' AND `sts` NOT LIKE '%2%' -- 処理待ちのみ（２を含まず、0を含むもの） ) AS `a` ); |
結果
2-4のSQLを「INの条件」としてさらに入れ子にして抽出する必要があるのでちょいややこしいですが、実現することができました。
ステータスを縦方向のレコードとして持たせてもきちんと抽出でき一安心。これは他でも使えそうな技だと思うので、ここに書いたことだけを覚えておこう（笑）