1対Nのテーブル2つがある場合、N側のテーブルはデータを縦持ちするはず。
しかしながら、1側のテーブルに合わせて、1行で表示したい場合の小ネタ。
せっかくなので、MariaDB10.7の新機能も添えて紹介。
たとえば、こんなテーブルとデータがあります。
CREATE TABLE `question` ( `question_id` int(11) NOT NULL COMMENT '問題ID', `question_text` text NOT NULL COMMENT '本文', PRIMARY KEY (`question_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='問題'> select * from question;+-------------+--------------------------------------------------------------------------------------------------------------+| question_id | question_text |+-------------+--------------------------------------------------------------------------------------------------------------+| 1 | しゃっくりはある調味料をなめると止まります。ある調味料とはなんでしょう？ |+-------------+--------------------------------------------------------------------------------------------------------------+ |
CREATE TABLE `question_select` ( `question_id` int(11) NOT NULL COMMENT '問題ID', `select_id` int(11) NOT NULL COMMENT '選択肢ID', `select_text` text NOT NULL COMMENT '選択肢内容', PRIMARY KEY (`question_id`,`select_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='選択肢'> select * from question_select;+-------------+-----------+-------------+| question_id | select_id | select_text |+-------------+-----------+-------------+| 1 | 1 | お酢 || 1 | 2 | 砂糖 || 1 | 3 | 醤油 || 1 | 4 | 塩 |+-------------+-----------+-------------+ |
これを1行で表示したい場合、以下のようにやる。
> with t as ( select t1.question_id, t1.question_text, sformat('[{}]', group_concat(distinct json_object('select_id', t2.select_id, 'select_text', t2.select_text) order by t2.select_id)) as select_list from question as t1 join question_select as t2 on t1.question_id = t2.question_id group by t1.question_id, t1.question_text ) select question_id, question_text, json_value(select_list, '$[0].select_id') as select_id_1, json_value(select_list, '$[0].select_text') as select_text_1, json_value(select_list, '$[1].select_id') as select_id_2, json_value(select_list, '$[1].select_text') as select_text_2, json_value(select_list, '$[2].select_id') as select_id_3, json_value(select_list, '$[2].select_text') as select_text_3, json_value(select_list, '$[3].select_id') as select_id_4, json_value(select_list, '$[3].select_text') as select_text_4 from t ; |
+-------------+--------------------------------------------------------------------------------------------------------------+-------------+---------------+-------------+---------------+-------------+---------------+-------------+---------------+| question_id | question_text | select_id_1 | select_text_1 | select_id_2 | select_text_2 | select_id_3 | select_text_3 | select_id_4 | select_text_4 |+-------------+--------------------------------------------------------------------------------------------------------------+-------------+---------------+-------------+---------------+-------------+---------------+-------------+---------------+| 1 | しゃっくりはある調味料をなめると止まります。ある調味料とはなんでしょう？ | 1 | お酢 | 2 | 砂糖 | 3 | 醤油 | 4 | 塩 |+-------------+--------------------------------------------------------------------------------------------------------------+-------------+---------------+-------------+---------------+-------------+---------------+-------------+---------------+ |
ポイントはgroup_concatで横持ちに変換する際に、JSON配列にしてしまうこと。
集計するときに結構使える技。
今回はMariaDBの10.7新機能であるsformat関数を使ってみましたが、concat関数でも代用可能です。
MySQLの場合は、「->>」とかで、JSONから値を取り出す感じですかね。
以上