お世話になります。
先日テーブルの集計について教えていただき、下記集計結果を取得することができたのですが、
新たに自由形50~平泳ぎ50までの合計を追加したいのです。(一人で4種目参加する場合は4と数える)
テーブル1
AreaID AreaName
---------------------
1 北海道
2 東北
テーブル3
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 テーブル3 where ItemID1<>'' or ItemID2<>'' or ItemID3<>'' or ItemID4<>'' or (ItemID1<>'' and ItemID2<>'') ・・・
バージョンはMySQL4.1.18です。
よろしくお願い致します。
前回、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」で判別します。「=」や「<>」では、通常、判別できません。
#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関数のところだけ変更すればいいことになります。
お礼
chukenkenkouさん、いつもありがとうございます。 前回に続き大変勉強になりました。 nullの使い方間違っていたんですね。ご指導ありがとうございました。 また、select ID,AreaID,1…の1について調べてみて「定数を指定」というのがあったのですが、よく理解できませんでした。 countで使うためなのでしょうか。 よろしければご教授ください。