create table 休暇(
Code number(1),seq number(1),年 number(4),月 number(1),
d1 number(1),d2 number(1),d3 number(1),d4 number(1),d5 number(1),d6 number(1),d7 number(1),d8 number(1),
d9 number(1),d10 number(1),d11 number(1),d12 number(1),d13 number(1),d14 number(1),d15 number(1),d16 number(1),
d17 number(1),d18 number(1),d19 number(1),d20 number(1),d21 number(1),d22 number(1),d23 number(1),d24 number(1),
d25 number(1),d26 number(1),d27 number(1),d28 number(1),d29 number(1),d30 number(1),d31 number(1));
--2003年1月20日
insert into 休暇 values(1,1,2003,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0);
--2004年4月21日
insert into 休暇 values(1,2,2004,4,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0);
--2003年1月2日
insert into 休暇 values(2,1,2003,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
--2005年1月30日
insert into 休暇 values(2,2,2005,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0);
--2004年5月30日
insert into 休暇 values(3,1,2004,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0);
--2004年4月1日
insert into 休暇 values(4,1,2004,4,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
commit;
--■■■unionを使う方法■■■
select distinct Code
from (select Code,to_char(年) || to_char(月,'FM09') || '01' as 休日 from 休暇 where d1=1
union all select Code,to_char(年) || to_char(月,'FM09') || '02' as 休日 from 休暇 where d2=1
union all select Code,to_char(年) || to_char(月,'FM09') || '03' as 休日 from 休暇 where d3=1
union all select Code,to_char(年) || to_char(月,'FM09') || '04' as 休日 from 休暇 where d4=1
union all select Code,to_char(年) || to_char(月,'FM09') || '05' as 休日 from 休暇 where d5=1
union all select Code,to_char(年) || to_char(月,'FM09') || '06' as 休日 from 休暇 where d6=1
union all select Code,to_char(年) || to_char(月,'FM09') || '07' as 休日 from 休暇 where d7=1
union all select Code,to_char(年) || to_char(月,'FM09') || '08' as 休日 from 休暇 where d8=1
union all select Code,to_char(年) || to_char(月,'FM09') || '09' as 休日 from 休暇 where d9=1
union all select Code,to_char(年) || to_char(月,'FM09') || '10' as 休日 from 休暇 where d10=1
union all select Code,to_char(年) || to_char(月,'FM09') || '11' as 休日 from 休暇 where d11=1
union all select Code,to_char(年) || to_char(月,'FM09') || '12' as 休日 from 休暇 where d12=1
union all select Code,to_char(年) || to_char(月,'FM09') || '13' as 休日 from 休暇 where d13=1
union all select Code,to_char(年) || to_char(月,'FM09') || '14' as 休日 from 休暇 where d14=1
union all select Code,to_char(年) || to_char(月,'FM09') || '15' as 休日 from 休暇 where d15=1
union all select Code,to_char(年) || to_char(月,'FM09') || '16' as 休日 from 休暇 where d16=1
union all select Code,to_char(年) || to_char(月,'FM09') || '17' as 休日 from 休暇 where d17=1
union all select Code,to_char(年) || to_char(月,'FM09') || '18' as 休日 from 休暇 where d18=1
union all select Code,to_char(年) || to_char(月,'FM09') || '19' as 休日 from 休暇 where d19=1
union all select Code,to_char(年) || to_char(月,'FM09') || '20' as 休日 from 休暇 where d20=1
union all select Code,to_char(年) || to_char(月,'FM09') || '21' as 休日 from 休暇 where d21=1
union all select Code,to_char(年) || to_char(月,'FM09') || '22' as 休日 from 休暇 where d22=1
union all select Code,to_char(年) || to_char(月,'FM09') || '23' as 休日 from 休暇 where d23=1
union all select Code,to_char(年) || to_char(月,'FM09') || '24' as 休日 from 休暇 where d24=1
union all select Code,to_char(年) || to_char(月,'FM09') || '25' as 休日 from 休暇 where d25=1
union all select Code,to_char(年) || to_char(月,'FM09') || '26' as 休日 from 休暇 where d26=1
union all select Code,to_char(年) || to_char(月,'FM09') || '27' as 休日 from 休暇 where d27=1
union all select Code,to_char(年) || to_char(月,'FM09') || '28' as 休日 from 休暇 where d28=1
union all select Code,to_char(年) || to_char(月,'FM09') || '29' as 休日 from 休暇 where d29=1
union all select Code,to_char(年) || to_char(月,'FM09') || '30' as 休日 from 休暇 where d30=1
union all select Code,to_char(年) || to_char(月,'FM09') || '31' as 休日 from 休暇 where d31=1)
where 休日 between '20030121' and '20040420';
--■■■グループ化する方法■■■
select Code
from 休暇
group by Code
having
max(decode(d1, 1,to_char(年) || to_char(月,'FM09') || '01')) between '20030121' and '20040420'
or max(decode(d1, 2,to_char(年) || to_char(月,'FM09') || '02')) between '20030121' and '20040420'
or max(decode(d1, 3,to_char(年) || to_char(月,'FM09') || '03')) between '20030121' and '20040420'
or max(decode(d1, 4,to_char(年) || to_char(月,'FM09') || '04')) between '20030121' and '20040420'
or max(decode(d1, 5,to_char(年) || to_char(月,'FM09') || '05')) between '20030121' and '20040420'
or max(decode(d1, 6,to_char(年) || to_char(月,'FM09') || '06')) between '20030121' and '20040420'
or max(decode(d1, 7,to_char(年) || to_char(月,'FM09') || '07')) between '20030121' and '20040420'
or max(decode(d1, 8,to_char(年) || to_char(月,'FM09') || '08')) between '20030121' and '20040420'
or max(decode(d1, 9,to_char(年) || to_char(月,'FM09') || '09')) between '20030121' and '20040420'
or max(decode(d1,10,to_char(年) || to_char(月,'FM09') || '10')) between '20030121' and '20040420'
or max(decode(d1,11,to_char(年) || to_char(月,'FM09') || '11')) between '20030121' and '20040420'
or max(decode(d1,12,to_char(年) || to_char(月,'FM09') || '12')) between '20030121' and '20040420'
or max(decode(d1,13,to_char(年) || to_char(月,'FM09') || '13')) between '20030121' and '20040420'
or max(decode(d1,14,to_char(年) || to_char(月,'FM09') || '14')) between '20030121' and '20040420'
or max(decode(d1,15,to_char(年) || to_char(月,'FM09') || '15')) between '20030121' and '20040420'
or max(decode(d1,16,to_char(年) || to_char(月,'FM09') || '16')) between '20030121' and '20040420'
or max(decode(d1,17,to_char(年) || to_char(月,'FM09') || '17')) between '20030121' and '20040420'
or max(decode(d1,18,to_char(年) || to_char(月,'FM09') || '18')) between '20030121' and '20040420'
or max(decode(d1,19,to_char(年) || to_char(月,'FM09') || '19')) between '20030121' and '20040420'
or max(decode(d1,20,to_char(年) || to_char(月,'FM09') || '20')) between '20030121' and '20040420'
or max(decode(d1,21,to_char(年) || to_char(月,'FM09') || '21')) between '20030121' and '20040420'
or max(decode(d1,22,to_char(年) || to_char(月,'FM09') || '22')) between '20030121' and '20040420'
or max(decode(d1,23,to_char(年) || to_char(月,'FM09') || '23')) between '20030121' and '20040420'
or max(decode(d1,24,to_char(年) || to_char(月,'FM09') || '24')) between '20030121' and '20040420'
or max(decode(d1,25,to_char(年) || to_char(月,'FM09') || '25')) between '20030121' and '20040420'
or max(decode(d1,26,to_char(年) || to_char(月,'FM09') || '26')) between '20030121' and '20040420'
or max(decode(d1,27,to_char(年) || to_char(月,'FM09') || '27')) between '20030121' and '20040420'
or max(decode(d1,28,to_char(年) || to_char(月,'FM09') || '28')) between '20030121' and '20040420'
or max(decode(d1,29,to_char(年) || to_char(月,'FM09') || '29')) between '20030121' and '20040420'
or max(decode(d1,30,to_char(年) || to_char(月,'FM09') || '30')) between '20030121' and '20040420'
or max(decode(d1,31,to_char(年) || to_char(月,'FM09') || '31')) between '20030121' and '20040420';