ORACLE WITH AS 的使用

--WITH AS 的用法

--创建表
CREATE TABLE CHECK_IN 
(CID NUMBER PRIMARY KEY,
CSDATE DATE,
CEDATE DATE)

SELECT * FROM CHECK_IN;
DELETE  FROM CHECK_IN;
INSERT INTO CHECK_IN VALUES(1,'01-JAN-2021','13-JAN-2021');
INSERT INTO CHECK_IN VALUES(2,'13-JAN-2021','13-FEB-2021');
INSERT INTO CHECK_IN VALUES(3,'13-FEB-2021','13-MAR-2021');
INSERT INTO CHECK_IN VALUES(4,'04-APR-2021','13-APR-2021');
INSERT INTO CHECK_IN VALUES(5,'13-APR-2021','13-JUN-2021');
INSERT INTO CHECK_IN VALUES(6,'13-JUN-2021','13-JUL-2021');
INSERT INTO CHECK_IN VALUES(7,'20-JUL-2021','25-AUG-2021');
INSERT INTO CHECK_IN VALUES(8,'25-AUG-2021','13-SEP-2021');
INSERT INTO CHECK_IN VALUES(9,'3-SEP-2021','13-OCT-2021');
INSERT INTO CHECK_IN VALUES(10,'7-OCT-2021','13-OCT-2021');
INSERT INTO CHECK_IN VALUES(11,'16-NOV-2021','23-NOV-2021');

--定位连续值的范围 
SELECT A.* FROM CHECK_IN A,CHECK_IN B WHERE B.CSDATE=A.CEDATE

SELECT C.*,LAG(CEDATE)OVER(ORDER BY CEDATE)上次结束时间 FROM CHECK_IN C;
WITH A AS 
(SELECT C.*,LEAD(CSDATE)OVER(ORDER BY CSDATE)下次开始时间 FROM CHECK_IN C)
SELECT * FROM A WHERE A.下次开始时间<=A.CEDATE;

WITH A AS 
(SELECT C.*, LAG(CEDATE)OVER(ORDER BY CEDATE)上次结束时间 FROM CHECK_IN C),
B AS (SELECT A.*, CASE WHEN COALESCE(A.上次结束时间,TO_DATE('0001-01-01','yyyy-MM-dd'))>=A.CSDATE 
THEN 0 ELSE 1 END 状态 FROM A),
C AS (SELECT B.*,SUM(B.状态)OVER(ORDER BY CID) AS 组 FROM B)
SELECT C.组,MIN(C.CSDATE)开始时间,MAX(C.CEDATE)结束时间 FROM C GROUP BY C.组

SELECT A.组,MAX(CEDATE),MIN(CSDATE)FROM(
SELECT SUM(B.状态)OVER(ORDER BY CSDATE)组,B.* FROM(
SELECT C.*,LAG(CEDATE)OVER(ORDER BY CSDATE) AS 上次结束时间,
CASE WHEN LAG(CEDATE)OVER(ORDER BY CSDATE)>=C.CSDATE THEN 0 ELSE 1 END AS 状态
   FROM CHECK_IN C)B)A GROUP BY A.组 ORDER BY A.组;
   
   
   
--LISTAGG+九九乘法表
WITH A AS 
     (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL<=9)
     
     SELECT LVA.LV,LVB.LV,
      TO_CHAR(LVA.LV||'*'||LVB.LV||'='||LVA.LV*LVB.LV) 
      FROM A LVA,A LVB
      WHERE LVA.LV<=LVB.LV
      ORDER BY LVB.LV,LVA.LV
   

WITH A AS 
     (SELECT LEVEL AS LV FROM DUAL CONNECT BY LEVEL<=9),
     B AS
     (SELECT LVA.LV AS AVALUE,LVB.LV AS BVALUE,
      TO_CHAR(LVA.LV||'*'||LVB.LV||'='||RPAD(LVA.LV*LVB.LV,2,'    '))AS THISVALUE
      FROM A LVA,A LVB
      WHERE LVA.LV<=LVB.LV
      ORDER BY LVB.LV,LVA.LV
      )
      SELECT B.BVALUE,
      LISTAGG(B.THISVALUE,'  ')
      WITHIN GROUP(ORDER BY B.AVALUE)九九乘法表 
      FROM B GROUP BY B.BVALUE

--此外，还可以使用WITH AS 作为子查询。日后有空会更新