MySQLのCOUNTを使って件数0のデータを含めたデータを表示させるSELECT文について

Question

お世話になります。Mysql(バージョン5.7.23)あるSQL文を勉強しております。
以下のようなテーブルとデータを用意しました。

CREATE TABLE main_table (
  `mainID` int(11) NOT NULL,
  `name` varchar(50)  NOT NULL,
  PRIMARY KEY (`mainID`)
);

CREATE TABLE sub_table (
  `subID` int(11) NOT NULL,
  `main1` int(11)  NOT NULL,
  `main2` int(11)  NOT NULL DEFAULT 0,
  `main3` int(11)  NOT NULL DEFAULT 0,
  PRIMARY KEY (`subID`)
)

main_table(mainIDは1から始まります)

mainID	name
1	name1
2	name2
3	name3
4	name4
5	name5
6	name6
7	name7
8	name8
…	…

sub_table（subIDは1から始まります）

subID	main1	main2	main3
1	1	2	0
2	2	1	4
3	4	5	6
4	3	1	2
5	8	1	0
…	…	…	…

このとき各mainIDに対してmain1またはmain2またはmain3に登録されているデータのSELECT文を考えています。
例えば、mainIDが8まで、subIDが5まで登録されていたら以下のように出力されるSELECT文を考えています。

mainID	name	件数
1	name1	4(subID=1のmain1、subID=2のmain2、subID=4のmain2、subID=5のmain2の計4件)
2	name2	3(subID=1のmain2、subID=2のmain1、subID=4のmain3の計3件)
3	name3	1(subID=4のmain1の1件)
4	name4	2(subID=2のmain3、subID=3のmain1の計2件)
5	name5	1(subID=3のmain2の1件)
6	name6	1(subID=3のmain3の1件)
7	name7	0(データないので0件)
8	name8	1(subID=5のmain1の1件)

私が考えたSQL文は以下の内容ですが、もっと簡単な文にできるかどうか教えてください。
よろしくお願いします。

SELECT
  `mainID`
  , name
  , IFNULL(M1.c1,0) + IFNULL(M2.c2,0) + IFNULL(M3.c3,0) 件数
FROM
  mysql.main_table 
LEFT JOIN (SELECT main1, COUNT(main1) c1 FROM sub_table GROUP BY main1) M1 ON M1.main1 = mainID
LEFT JOIN (SELECT main2, COUNT(main2) c2 FROM sub_table GROUP BY main2) M2 ON M2.main2 = mainID
LEFT JOIN (SELECT main3, COUNT(main3) c3 FROM sub_table GROUP BY main3) M3 ON M3.main3 = mainID
ORDER BY
  `mainID`

Answer 1

例えば

select mainID,name,coalesce(cnt,0) as cnt from main_table as t1
left join (
select main,sum(cnt) as cnt from (
select main1 as main,count(*) as cnt from sub_table group by main
union all select main2 as main,count(*) from sub_table group by main
union all select main3 as main,count(*) from sub_table group by main
) as t3 group by main
) as t2 on t1.mainID=t2.main

しかしこれでは非効率なので

CREATE TABLE sub2 (
  `subID` int(11) NOT NULL,
  `main` int(11)  NOT NULL,
  `sort`  int(11)  NOT NULL,
  unique (`subID`,`sort`)
);

insert into sub2 values
(1,1,1),
(1,2,2),
(1,0,3),
(2,2,1),
(2,1,2),
(2,4,3),
(3,4,1),
(3,5,2),
(3,6,3),
(4,3,1),
(4,1,2),
(4,2,3),
(5,8,1),
(5,1,2),
(5,0,3);

として

select mainID,name,coalesce(cnt,0) as cnt from main_table as t1
left join (
select main,count(*) as cnt from sub2 group by main
) as t2 on t1.mainID=t2.main

とするとよい
ちなみに、sub2からsub_tableのような表示にするには

select subID
,sum((sort=1)*main) as main1
,sum((sort=2)*main) as main2
,sum((sort=3)*main) as main3
from sub2
group by subID

Comments

[reprepstu]

テーブルの構成まで考えていただき、ありがとうございます。
教えていただいた内容を実行したら、思い通りの結果になりました。

Answer 2

sub_tableを
subID int(11) NOT NULL,
mainID int(11) NOT NULL,
main1とかmain2とかの情報 int(11) NOT NULL
の構成にしたほうがいいとおもいます

Comments

[reprepstu]

すみません、自宅で適当にtableを作成したので、他の人が見たら不自然なカラムになっていると思います。
よろしければ、提案して頂いたtableの構成にしたほうが良いと思った理由を教えていただけますか?

[takg]

提示なさっている構成は、今回のご質問のようにクエリが書きにくかったり、あとは拡張性が低かったりといったデメリットがあると思います。
もちろんreprepstuさんの要件に依るので、一概には言えませんが。
行持ち、列持ちで検索するとよいと思います。
参考:
http://gihyo.jp/dev/serial/01/sql_academy2/000302

[reprepjqu]

連絡が遅くなり申し訳ありません。
教えていただきありがとうございます。

Answer 3

結合の演算子は＝ばかりじゃないのです。

SELECT mainID, `name`, count(distinct subID)
FROM  main_table m LEFT JOIN sub_table s 
      on  m.mainID in (s.main1, s.main2, s.main3)
GROUP BY mainID, `name`
ORDER BY mainID

Comments

[yambejp]

質問の趣旨から言って満たしているとは思いますけど
この場合subIDごとにmain1,main2,main3がユニークであるという条件が必要です。

[sazi]

ユニークでない場合にsubIDを別々にカウントするというのが要件でなければ、ユニークでなくともsubidは重複して取得されるわけではないですし、さらにcount()でdistinctしているので問題無いと思うのですが。

[sazi]

ああ、ユニークでない場合は、元のSQLと違う結果になりますね。
でも、カウントの意図としては別にカウントされちゃうのは意図するところではないと思いますけどね。

[reprepstu]

＝だけで結合しようとばかり考えていました。
教えていただき、ありがとうございます。
因みに、subIDごとにmain1,main2,main3がユニークでないのを想定でした。情報が不足して申し訳ございません。
教えて頂いたSQLを実行しましたら、自分が考えたものと同じ結果になりました。
ありがとうございます。