こんな感じにしたい
+------------------------------+----------------------+
| title | category |
+------------------------------+----------------------+
| おまじない | 姉 |
| お留守番 | 義妹 |
| まくあいげき | イチャラブ |
| よびだし | 妹 |
| れっつすたでぃ | 従兄弟 |
| イかせたい交換条件 | 近親相姦 |
| バカンス | 褐色 |
| ピンチランナーズHIGH | 褐色,陸上,方言 |
| フラッピーキャット | ギャル |
| 近距離注意予報 | 妹,パイパン |
+------------------------------+----------------------+
例えば上みたいな感じのDBがあったとして(何のデータかは聞かないこと)、次のようにしたいとする。
+------------------------------+----------------------+
| title | category |
+------------------------------+----------------------+
| おまじない | 姉 |
| お留守番 | 義妹 |
| まくあいげき | イチャラブ |
| よびだし | 妹 |
| れっつすたでぃ | 従兄弟 |
| イかせたい交換条件 | 近親相姦 |
| バカンス | 褐色 |
| ピンチランナーズHIGH | 方言 |
| ピンチランナーズHIGH | 陸上 |
| ピンチランナーズHIGH | 褐色 |
| フラッピーキャット | ギャル |
| 近距離注意予報 | 妹 |
| 近距離注意予報 | パイパン |
+------------------------------+----------------------+
本来せっかくのDBに対してCSVで値を入れることがそもそも勧められないことらしいですが、言われてなきゃ気づかねーよって感じです。DB構築する前に教えといてくださいよ。
ちなみにこの様に横持ち→縦持ちに変換することをピボットって言います。
どうやるのか
別のOSSDBや商用RDBMSならそれようの関数が用意されているのですが、MySQLにはそれがないようです。私はMySQLしか触ったことがない情弱なので、少々遠回りする必要があります。
select title, substring_index(substring_index(category, ',', CS), ',', - 1) as category
from
(
select 3 as CS, title, category
from
master_table
limit 10
) as t1
where
length(category) - length(replace(category, ',', '')) >= 2;
+------------------------------+----------------------+
| title | category |
+------------------------------+----------------------+
| ピンチランナーズHIGH | 方言 |
+------------------------------+----------------------+
まずはカンマが2つの行を出力し、それに対して右端(3つ目)の値を出力します。
もう御察しだと思いますが、これをあと2回繰り返すだけです。
カンマが1つの行を出力し、右端の値を出力します。
select
title, substring_index(substring_index(category, ',', CS), ',', - 1) as category
from
(
select 2 as CS, title, category
from
master_table
limit 10
) as t2
where
length(category) - length(replace(category, ',', '')) >= 1;
+------------------------------+----------------------+
| title | category |
+------------------------------+----------------------+
| ピンチランナーズHIGH | 陸上 |
| 近距離注意予報 | パイパン |
+------------------------------+----------------------+
3回目は割愛。
最後にunionでまとめる
一応最終的なクエリを載せておきます。
select *
from
(
select title, substring_index(substring_index(category, ',', CS), ',', - 1) as category
from
(
select 3 as CS, title, category
from master_table limit 10
) as t1
where
length(category) - length(replace(category, ',', '')) >= 2
union
select title, substring_index(substring_index(category, ',', CS), ',', - 1) as category
from
(
select 2 as CS, title, category
from master_table limit 10
) as t2
where
length(category) - length(replace(category, ',', '')) >= 1
union
select
title, substring_index(substring_index(category, ',', CS), ',', - 1) as category
from
(
select 1 as CS, title, category
from
master_table
limit 10
) as t3
where
length(category) - length(replace(category, ',', '')) >= 0
) T
order by title;
これは蛇足なのですが、union allだと重複を許し、unionは重複を削除するためソートを行っています。
カンマの数が増える分だけクエリの数を増やす必要があるので、正直面倒臭い。
やはりPostgreを学ぶ必要があるのでしょうか。