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から値を取り出す感じですかね。
以上