質問: ``` CREATE TABLE M_SHOP ( SHOP_ID NUMBER(3,0) NOT NULL, SHOP_NAME VARCHAR2(60) NOT NULL, APLY_DATE VARCHAR2(8) NOT NULL ); insert into M_SHOP(SHOP_ID,SHOP_NAME,APLY_DATE) values (1,'A店','20190601'); insert into M_SHOP(SHOP_ID,SHOP_NAME,APLY_DATE) values (2,'B店','20190601'); insert into M_SHOP(SHOP_ID,SHOP_NAME,APLY_DATE) values (3,'C店','20190601'); insert into M_SHOP(SHOP_ID,SHOP_NAME,APLY_DATE) values (1,'A1店','20190901'); insert into M_SHOP(SHOP_ID,SHOP_NAME,APLY_DATE) values (2,'B1店','20190901'); insert into M_SHOP(SHOP_ID,SHOP_NAME,APLY_DATE) values (4,'D1店','20190901'); insert into M_SHOP(SHOP_ID,SHOP_NAME,APLY_DATE) values (1,'A2店','20191001'); insert into M_SHOP(SHOP_ID,SHOP_NAME,APLY_DATE) values (2,'B2店','20191001'); insert into M_SHOP(SHOP_ID,SHOP_NAME,APLY_DATE) values (5,'E2店','20191001'); ```
回答: 手元にOracle環境がないため未検証です。 WITH句はOracle限定:SQLだけでカレンダー出力 - Qiitaを丸々利用しています。 ``` with T1 as ( SELECT start_date + ROWNUM -1 calendar FROM ( SELECT TRUNC( TO_DATE('20190830','YYYYMMDD') -- 表示開始の年月日を指定 ) start_date, 3 days -- 表示したい日数を指定 FROM DUAL ) ,ALL_CATALOG -- 表示したい日数分の行数があるテーブルを指定 = ROUNUMで利用 WHERE ROWNUM <= days ) select T1.*, M_SHOP.* from T1, M_SHOP where APLY_DATE = ( select MAX(APLY_DATE) from M_SHOP where APLY_DATE <= to_char(T1.calendar, 'YYYYMMDD') ) order by T1.calendar, M_SHOP.SHOP_ID ; ```