mysqlでカンマデータのgroup by

質問

初めての質問になります。

以下のようなテーブルで、dataカラムにカンマ区切りのデータを保存しているのですが、こちらはgroup byやcount等を使ってSQL一発で集計結果を取得できるのでしょうか？

SELECT count(*) FROM TEST WHERE FIND_IN_SET('1', data)で1から順番に取得は出来るのですが、数が多いとちょっといかがなものかと思い、質問させていただきました。
環境：mysql5.5

------------
｜data
------------
｜1,2,3
------------
｜1,2,10
------------
｜1,2,3,10
------------

期待する結果は以下のような感じです。

1｜3
---------
2｜3
---------
3｜2
---------
10｜2

回答No.1

yambejp · ベストアンサー

＞dataカラムにカンマ区切りのデータを保存している

いや、SQLでそれはあり得ない・・・
こうしてみて

//テストデータ
create table tbl(id int not null auto_increment primary key,groupid int,data int);
insert into tbl(groupid,data) values(1,1),(1,2),(1,3),(2,1),(2,2),(2,10),(3,1),(3,2),(3,3),(3,10);

//カンマ区切り表示
select groupid,group_concat(data order by data) as datas from tbl group by groupid;

//data単位で集計
select data,count(*) as count from tbl group by data;

お礼

早速のご回答ありがとうございました！
よく求人サイトなどで、「未経験可」「交通費支給」「時給1000円以上」といったオプションがあると思うのですが、そのデータをカンマ区切りで保存していました。
それで、そのオプションにマッチする企業が何件あるか？みたいな感じで使おうと思っていました。

ですので実際のテーブルはこんな感じです。（一部カラム省略）

create table tbl
(
conpany_id int not null auto_increment primary key,
conpany_name text not null,
service varchar(26),
・・・
);

// オプション配列
$options = array(1 = '未経験可', 2 = '交通費支給', 3 = '時給1000円以上', ・・・);

基本的にテーブル設計がイマイチだったんですね。。
tblテーブルからserviceカラムを削除し、ご教授いただいた項目で別テーブルを作成することで解決しました！

group_concatは今まで使ったことが無く、テーブル設計もまだまだ未熟なので非常に良い勉強になりました！
ありがとうございました！