カンマ区切りで登録されたデータからランキングは出せますでしょうか？

Question

以下のデータ形式の場合に、tag のランキングを出したい。
つまりテストが多い教科順に出力。
想定としては以下
教科,回数
算数,4
英語,3
国語,2
理科,1

CREATE TABLE `post` (
  `id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `tag` varchar(500) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
 
--
-- テーブルのデータのダンプ `post`
--
 
INSERT INTO `post` (`id`, `name`, `tag`) VALUES
(1, '朝のテスト', '国語,算数'),
(2, '昼のテスト', '国語,算数,理科,英語'),
(3, '研究テーマ', '算数'),
(4, '夏季テスト', '算数,英語'),
(5, '冬季テスト', '英語');

Answer 1

結論だけ示します。

select kamoku,count(id) as kaisuu from (
select id,substring_index(tag,',',1) as kamoku from post 
union select id,substring_index(substring_index(tag,',',2),',',-1) as kamoku from post 
union select id,substring_index(substring_index(tag,',',3),',',-1) as kamoku from post
union select id,substring_index(substring_index(tag,',',4),',',-1) as kamoku from post 
union select id,substring_index(substring_index(tag,',',5),',',-1) as kamoku from post
) as test group by kamoku order by kaisuu desc;

これの出力を以下に示します。

+--------+--------+
| kamoku | kaisuu |
+--------+--------+
| 算数   |      4 |
| 英語   |      3 |
| 国語   |      2 |
| 理科   |      1 |
+--------+--------+
4 rows in set (0.00 sec)

SQLを組み立てたときの思考回路

まず、科目とidの表がほしいと考えます。（擬似的に「第１正規形」に変換します）（第1正規形はデータベースの分野の専門用語です）

substring_indexを使うことでできることがわかりました。使い方は下の参考URLを見てください。
unionから始まる行がカンマ区切りで書かれる科目の個数分必要です。10科目書かれるなら10行必要です。行の中央付近で行ごとに2,3,4,5と増えているところをunionの行を増やして2,3,4,5,6,7,8,9,10とする必要があります。

select id,substring_index(tag,',',1) as kamoku from post 
union select id,substring_index(substring_index(tag,',',2),',',-1) as kamoku from post 
union select id,substring_index(substring_index(tag,',',3),',',-1) as kamoku from post
union select id,substring_index(substring_index(tag,',',4),',',-1) as kamoku from post 
union select id,substring_index(substring_index(tag,',',5),',',-1) as kamoku from post;

下のような結果が得られます。

+----+--------+
| id | kamoku |
+----+--------+
|  1 | 国語   |
|  2 | 国語   |
|  3 | 算数   |
|  4 | 算数   |
|  5 | 英語   |
|  1 | 算数   |
|  2 | 算数   |
|  4 | 英語   |
|  2 | 理科   |
|  2 | 英語   |
+----+--------+
10 rows in set (0.00 sec)

あとはkamokuの出現数を求めるだけです。この表をfrom句に入れます（asで別名をつけないと怒られるのでas testってつけました。

select kamoku,count(id) as kaisuu from (ここに入れる) as test group by kamoku order by kaisuu desc;

そうすると最初に書いた結論の通りのSQL文になり、結果が出ます。

参考URL:

• http://yakinikunotare.boo.jp/orebase2/db/mysql/pickup_part_of_string_like_split_function

カンマ区切りの文字を分割する方法を調べた

• https://www.searchlight8.com/mysql_find_in_set/

この質問のコードがあった

Comments

[Orlofsky]

URLは https://teratail.com/help/question-tips#questionTips3-7 の [リンク] で [リンク先の説明](http...) に修正してください。

[ktl2018]

ご指摘ありがとうございます。URLに関する記述を修正いたしました。

[akasatanaha]

ご回答ありがとうございました！
まさに求めていたものが実現いたしました。