同じ構成のテーブルの条件付き結合

質問

同じ構成のテーブルがテーブル1～テーブル6まであり、6つのテーブルを集計するSQLを作成しています。
カラムは
ID、名前、日付1、日付2、ステータス1、ステータス2
です。

IDがプライマリーキーになっており、テーブル1にしかないIDや全てのテーブルに登録されているIDなどもあり、IDの一覧を作成したいです。
IDが重複していた場合、名前はどれを残しても構わないのですが(どのテーブルか指定する必要があるのならばとりあえずテーブル番号が若い方の名前を残すようにしてください。)
日付1は最古の日付
日付2は最新の日付
ステータスは共に優先順位が1>2>0>nullとなっており優先順位が高いほうを残すようにし、ステータスが両方nullの場合は集計しないようにしたいです。

テーブル1
ID、名前、日付1、日付2、ステータス1、ステータス2
111、あああ、2001/1/1、2001/1/1、2、2
222、いいい、2001/1/1、2001/1/1、2、2
333、ううう、2001/1/1、2001/1/1、null、null
555、おおお、2001/1/1、2001/1/1、2、2

テーブル2
ID、名前、日付1、日付2、ステータス1、ステータス2
111、aaa、2002/2/2、2002/2/2、0、null
222、iii、2002/2/2、2002/2/2、1、0
333、uuu、2002/2/2、2002/2/2、null、null
444、eee、2002/2/2、2002/2/2、1、1

結果
ID、名前、日付1、日付2、ステータス1、ステータス2
111、あああ、2001/1/1、2002/2/2、2、2
222、いいい、2001/1/1、2002/2/2、1、2
444、eee、2002/2/2、2002/2/2、1、1
555、おおお、2001/1/1、2001/1/1、2、2

このようなことを6つのテーブルを集計して行いたいです。

SELECT t1.id,MIN(t1.日付1)as 日付1,MAX(t1.日付2)as 日付2,CASE When t1.ステータス1="1" then "1" else t1.ステータス1 end ,CASE When t1.ステータス2="1" then "1" else t1.ステータス2 end
FROM(
SELECT *
FROM テーブル1
UNION All
SELECT *
FROM テーブル2
UNION All
SELECT *
FROM テーブル3
UNION All
SELECT *
FROM テーブル4
UNION All
SELECT *
FROM テーブル5
UNION All
SELECT *
FROM テーブル6
)as t1
WHERE NOT(t1.ステータス1 IS null AND t1.ステータス2 IS null)
GROUP BY id

日付は出来ていると思うのですが、ステータスで1の優先がまだ出来ていないので修正お願いいたします。

回答No.1

yambejp · ベストアンサー

ステータスを順位付けするテーブルを作るとよいかと

/* 初期値 - 検証のためテーブル1のデータを増やしてます */
create table テーブル1(ID int,名前 varchar(20),日付1 date,日付2 date,ステータス1 int null,ステータス2 int null);
create table テーブル2(ID int,名前 varchar(20),日付1 date,日付2 date,ステータス1 int null,ステータス2 int null);
create table テーブル3(ID int,名前 varchar(20),日付1 date,日付2 date,ステータス1 int null,ステータス2 int null);
insert into テーブル1 values(111,'あああ','2001/1/1','2001/1/1',2,2),(222,'いいい','2001/1/1','2001/1/1',2,2),(333,'ううう','2001/1/1','2001/1/1',null,null),(555,'おおお','2001/1/1','2001/1/1',2,2),(666,'かかか','2001/1/1','2001/1/1',null,0);
insert into テーブル2 values(111,'aaa','2002/2/2','2002/2/2',0,null),(222,'iii','2002/2/2','2002/2/2',1,0),(333,'uuu','2002/2/2','2002/2/2',null,null),(444,'eee','2002/2/2','2002/2/2',1,1);
insert into テーブル3 values(111,'あああ','2001/1/1','2002/2/2',2,2),(222,'いいい','2001/1/1','2002/2/2',1,2),(444,'eee','2002/2/2','2002/2/2',1,1),(555,'おおお','2001/1/1','2001/1/1',2,2);

/* ステータスの順位付けテーブルを作る */
create table ステータス(status int null,rank int);
insert into ステータス values(1,1),(2,2),(0,3)

/* 集計 */
select
ID,日付1,日付2,st3.status as ステータス1,st4.status as ステータス2
from(
select ID,MIN(日付1) AS 日付1,MAX(日付2) AS 日付2
,MIN(st1.rank) as r1
,MIN(st2.rank) as r2
from (
select * from テーブル1
union all select * from テーブル2
union all select * from テーブル3
) uni1
left join ステータス as st1 on uni1.ステータス1=st1.status
left join ステータス as st2 on uni1.ステータス2=st2.status
GROUP BY ID
having r1 is not null or r2 is not null
) uni2
left join ステータス as st3 on uni2.r1=st3.rank
left join ステータス as st4 on uni2.r2=st4.rank

お礼

あ、昨日に引き続きありがとうございます。
おかげさまで完成させることが出来ました。