MySQLでの集計

質問

いつもお世話になっております。MySQLを利用しておりまして質問です。

同じ構成のテーブルが5つあり、選択されたテーブルを集計するSQLを作成しています。
カラムを簡単に構成を書くと
ID、名前、ステータス1、ステータス2
のような形です。

テーブル1、2、3が選択された場合
テーブル1と2でIDの重複のない一覧を作成し、IDが被っていた場合はステータスをそれぞれ大きいほうを保持するようにします。
そして次にテーブル1、2を合わせたテーブルとテーブル3で同じように集計をしたいのですが、ここがうまく出来ません。

実際の環境ではなく簡素化したものなのでおかしな点があるかもしれませんがこのようになっています。

SELECT TTT1.id,TTT1.名前,TTT1.st1,TTT1.st2
FROM(
　　　　SELECT id,名前,ステータス1 as st1,ステータス2 as st2
　　　　FROM(
　　　　　　　SELECT *
　　　　　　　FROM テーブル1 T1
　　　　　　　WHERE T1.id NOT IN(SELECT T2.id FROM テーブル2 T2)
　　　　　　　UNION All
　　　　　　　SELECT *
　　　　　　　FROM テーブル2 T2
　　　　　　　WHERE T2.id NOT IN(SELECT T1.id FROM テーブル T1)
　　　　) as TT1
　　　　UNION
　　　　SELECT id,名前,st1,st2
　　　　FROM(
　　　　　　　　SELECT T1.id,T1.名前,
　　　　　　　　(CASE T1.ステータス1 or T2.ステータス1 WHEN "2" THEN "2" WHEN "1" THEN "1" WHEN "0" THEN "0" ELSE null END) as st1,
　　　　　　　　(CASE T1.ステータス2 or T2.ステータス2 WHEN "2" THEN "2" WHEN "1" THEN "1" WHEN "0" THEN "0" ELSE null END) as st2,
　　　　　　　　FROM テーブル1 T1,テーブル2 T2
　　　　　　　　WHERE T1.id = T2.id AND NOT(T1.st1 IS null AND T1.st2 IS null AND T2.st1 IS null AND T2.st2 IS null)
) as TT2
)as TTT1
/*
UNION ALL
SELECT id,名前,st1,st2
FROM (TTT1とテーブル3を比較したもの)as TTT2
*/

/**/で囲ったところでTTT1が呼び出せれば、テーブル1のところをTTT1にし、テーブル2をテーブル3にすれば出来ると思うのですが、そういうことは無理なのでしょうか？

また集計するテーブルの数が不特定なので一つずつ集計をする形を取りましたが、
もっとこういう風にやればいいと言う方法はございますでしょうか？
よろしくお願いいたします。

回答No.6

o_chi_chi · ベストアンサー

>ステータスの優先順位が1>2>0>null

なら1を最大値になるよう変換し、最後にもとに戻すとか。
（うまい方法が見つからなかっただけですが。。。）

----
SELECT *
FROM テーブル1
↓
SELECT
ID,
When ステータス1='1' then '10' else ステータス1 end as S1,
ステータス2 as S2
FROM テーブル1

日付はDATETIME型ならmaxで問題ないと思います。

お礼

再度のご回答ありがとうございます。
これで出来たと思います！！
すごいシンプルになりましたね・・・。
う～ん、もっと勉強せねば。
ありがとうございました。

回答No.5

yambejp

あ、なんか違うので再チャレンジ

回答No.4

yambejp

＞優先順位が正しくは1>2>0>nullの順番です。

なるほど、ではこんな感じで
select id
, case when 1 IN (t1.st1,t2.st1,t3.st1) THEN 1
when 2 IN (t1.st1,t2.st1,t3.st1) THEN 2
when 0 IN (t1.st1,t2.st1,t3.st1) THEN 0
else null end as st1
, case when 1 IN (t1.st2,t2.st2,t3.st2) THEN 1
when 2 IN (t1.st2,t2.st2,t3.st2) THEN 2
when 0 IN (t1.st2,t2.st2,t3.st2) THEN 0
else null end as st2
from(
select id from t1
union select id from t2
union select id from t3
) uni
left join t1 using(id)
left join t2 using(id)
left join t3 using(id)

回答No.3

o_chi_chi

下記じゃだめでしょうか。

---
SELECT ID,MAX(ステータス1),MAX(ステータス2)
FROM
(SELECT *
FROM テーブル1
UNION All
SELECT *
FROM テーブル2
UNION All
SELECT *
FROM テーブル3)
GROUP BY ID

補足

回答ありがとうございます。
selectに条件を書くやり方もあったんですね。
TTT1の呼出し方法を聞こうと思っていたため、
細かい条件のところは自分でやろう思っていたので後出しになって申し訳ないのですが、
ステータスの優先順位が1>2>0>nullで、ステータス1、2が両方nullの場合は表示しません。
WHERE NOT(ステータス1 IS null AND ステータス2 IS null)
で両方nullの場合の排除は出来たのですが、selectのところでの優先順位のつけ方がわかりません。
再度よろしくお願いいたします。

あと日付のカラムがあるんですが最新はMAX(日付1)最古はMIN(日付2)で問題ないですか？

回答No.2

yambejp

ごめんなさい、私の書き方がわるかったかもしれません。
ステータスの値がどちらのテーブルに寄せるかによって名前をどちらから
呼ぶかがかわってくるということです。
名前をよばないならidのリストにleft join すればよいかと。
（最小値に-999というダミーをいれていますが、ステータスがもっと
低いのが最大値になるようなら調整が必要）

select id
,greatest(coalesce(t1.st1,-999),coalesce(t2.st1,-999),coalesce(t3.st1,-999)) as st1
,greatest(coalesce(t1.st2,-999),coalesce(t2.st2,-999),coalesce(t3.st2,-999)) as st2
from(
select id from t1
union select id from t2
union select id from t3
) uni
left join t1 using(id)
left join t2 using(id)
left join t3 using(id)

補足

どのテーブルが呼び出されるかが不確定なのでどれを優先っていうのはないのですが、逆にどれを優先しても構いません。
そして後出しになって大変申し訳ないのですが、ステータスの優先順位が正しくは1>2>0>nullの順番です。
対応出来ますでしょうか、よろしくお願いいたします。

回答No.1

yambejp

>IDが被っていた場合はステータスをそれぞれ大きいほうを保持

IDがかぶっていてステータスがかぶっていたらどっちをとるのでしょうか？

補足

回答ありがとうございます。
どちらでも構いません。
実際の環境では日付のカラムもいくつかあり、新しい日付を残すようにしたり、古い日付を残すようにしていたりするので、テーブル1を活かす、テーブル2を活かすというものではありません。
テーブル1のステータス1が0、ステータス2が2
テーブル2のステータス1が1、ステータス2が1
だった場合
ステータス1が1、ステータス2が2
を表示するようにしたいです。