複数テーブルの集計その２

質問

お世話になります。

先日テーブルの集計について教えていただき、下記集計結果を取得することができたのですが、
新たに自由形50～平泳ぎ50までの合計を追加したいのです。（一人で４種目参加する場合は４と数える）

テーブル１
AreaID 　 AreaName
---------------------
1 北海道
2 東北

テーブル３
ID AreaID　　　Name　　Item1　　　Item2　　Item3 Item4
---------------------------------------------------------
1　　　2　　　　鈴木　　　　2　　　　1　　　　4　　　　　3
2　　　1　　　　田中　　　　1　　　　2　　　　null　　　null
3　　　2　　　　伊藤　　　　3　　　　null　　　2　　　　4　

「集計結果」
　　　　　　参加人数　自由形50　自由形100　背泳ぎ50　平泳ぎ50 　追加部分
---------------------------------------------------------------
北海道　　　　 1　　　　　1　　　 　 　1　　　　　 0　　　　　 0　　　　　　 2
東北　　　　　　2　　　　　1　　　 　 　2　　　　　 2　　　　　 2　　　　　　 7　　

$sql="select　AreaName,
count(distinct ID) 参加人数,
count(case when Item=1 then 1 else null end) as 自由形50,
・・・略
from t1 as x
left join
(select ID,AreaID,1 as Item from t3 where Item1=1 or Item2=1 or Item3=1 or Item4=1
union all
select ID,AreaID,2 as Item from t3 where Item1=2 or Item2=2 or Item3=2 or Item4=2
) as y　on x.AreaID=y.AreaID
group by AreaName　order by x.AreaID
というようにおしえていただきました。

null以外を取得するSQLをカウントとselectに追加すればいいのかと思い試したのですが期待した値を取得できません。
count(case when Item=AAA then 1 else null end) as abc,と
union all
select ID,AreaID,AAA as Item from テーブル３　where ItemID1<>'' or ItemID2<>'' or ItemID3<>'' or ItemID4<>'' or (ItemID1<>'' and ItemID2<>'') ・・・

バージョンはMySQL4.1.18です。
よろしくお願い致します。

回答No.1

chukenkenkou · ベストアンサー

前回、SQLを提示した者です。
まず、前回のSQLをおさらいしましょう。

前回のSQLを、グループ化とcountをやめ、どういう行が作られているか確認してみましょう。

＜前回のSQL＞
select *
from t1 as x
left join
(
select ID,AreaID,1 as Item from t3 where Item1=1 or Item2=1 or Item3=1 or Item4=1
union all
select ID,AreaID,2 as Item from t3 where Item1=2 or Item2=2 or Item3=2 or Item4=2
union all
select ID,AreaID,3 as Item from t3 where Item1=3 or Item2=3 or Item3=3 or Item4=3
union all
select ID,AreaID,4 as Item from t3 where Item1=4 or Item2=4 or Item3=4 or Item4=4
) as y
on x.AreaID=y.AreaID
order by x.AreaID
;

上記のSQLでは、テーブル3の各人毎に、種目を「列で管理」していたものを、「別の行」で管理するようにしています。
今回、追加で求めるものは「のべの種目数」のようなので、上記で生成した行から、集計するcount関数を追加すれば実現可能と思います。

前回のSQLに追加するのは、
count(case when Item between 1 and 4 then 1 else null end) as 追加部分
だけです。

＜今回のSQL＞
select
AreaName,
count(distinct ID) as 参加人数,
count(case when Item=1 then 1 else null end) as 自由形50,
count(case when Item=2 then 1 else null end) as 自由形100,
count(case when Item=3 then 1 else null end) as 背泳ぎ50,
count(case when Item=4 then 1 else null end) as 平泳ぎ50,
count(case when Item between 1 and 4 then 1 else null end) as 追加部分
from t1 as x
left join
(
select ID,AreaID,1 as Item from t3 where Item1=1 or Item2=1 or Item3=1 or Item4=1
union all
select ID,AreaID,2 as Item from t3 where Item1=2 or Item2=2 or Item3=2 or Item4=2
union all
select ID,AreaID,3 as Item from t3 where Item1=3 or Item2=3 or Item3=3 or Item4=3
union all
select ID,AreaID,4 as Item from t3 where Item1=4 or Item2=4 or Item3=4 or Item4=4
) as y
on x.AreaID=y.AreaID
group by AreaName
order by x.AreaID
;

＜注意事項＞
質問者さんが考えた部分で、nullの扱いに勘違いがあるようなので、補足説明しておきます。
(1)nullと0バイト文字は別物です。
nullを判別するには、「列名 is null」や「列名 is not null」で判別します。「=」や「<>」では、通常、判別できません。

お礼

chukenkenkouさん、いつもありがとうございます。
前回に続き大変勉強になりました。
nullの使い方間違っていたんですね。ご指導ありがとうございました。

また、select ID,AreaID,1…の1について調べてみて「定数を指定」というのがあったのですが、よく理解できませんでした。
countで使うためなのでしょうか。
よろしければご教授ください。

回答No.2

chukenkenkou

#1回答者です。

>select ID,AreaID,1…の1について調べてみて「定数を指定」

ITEM1～ITEM4のいずれかに1が入っている行数分、種目IDとして1（定数で指定）を拾うようにしています。
同様に、ITEM1～ITEM4のいずれかに2が入っている行数分、種目IDとして2（定数で指定）を拾うようにしています。

しかし、前回までに提示したSQLは、種目IDが1～4を予め抽出してunionする仕組みにしていました。これでは、種目IDが追加された場合、その分だけ、unionするselect文を追加する必要がありました。
そこで、次のようなSQLにした方が、シンプルで分かりやすいですね。

＜SQL例＞
select
AreaName,
count(distinct ID) as 参加人数,
count(case when Item=1 then 1 else null end) as 自由形50,
count(case when Item=2 then 1 else null end) as 自由形100,
count(case when Item=3 then 1 else null end) as 背泳ぎ50,
count(case when Item=4 then 1 else null end) as 平泳ぎ50,
count(case when Item between 1 and 4 then 1 else null end) as 追加部分
from t1 as x
left join
(
select ID,AreaID,Item1 as Item from t3
union all
select ID,AreaID,Item2 as Item from t3
union all
select ID,AreaID,Item3 as Item from t3
union all
select ID,AreaID,Item4 as Item from t3
) as y
on x.AreaID=y.AreaID and Item is not null
group by AreaName
order by x.AreaID
;

unionするときは、単純にITEM1～ITEM4を、別の行として得ています。仮に種目IDが追加された場合は、selectのcount関数のところだけ変更すればいいことになります。

お礼

ご回答ありがとうございます。

定数の意味がなんとなくですがわかりました。
select文はもっと勉強します！
シンプルな構文のほうもさっそく試してみたいと思います。
本当にありがとうございました。