【oracle/语法】With语句的写法

Sample1：

with temp AS (
  select * from emp where rownum<11)
select * from temp

Result：

SQL> with temp AS (
  2    select * from emp where rownum<11)
  3  select * from temp;

        ID NAME                                            AGE     SALARY CREATE_TIME
---------- ---------------------------------------- ---------- ---------- ---------------------------------------------------------------------------
       561 NDRKPHCF                                         40      43533 07-9月 -21 06.32.42.000000 下午
       562 VOMTMOBLCBVKIGVOZSN                              33      23063 07-9月 -21 06.32.42.000000 下午
       563 GKDDBKTFAUON                                     63      14078 07-9月 -21 06.32.42.000000 下午
       564 MHCEIQMT                                         64      11360 07-9月 -21 06.32.42.000000 下午
       565 IVKRXCHYROE                                      28      38106 07-9月 -21 06.32.42.000000 下午
       566 GROURMS                                          25      33055 07-9月 -21 06.32.42.000000 下午
       567 BEUEKLUL                                         63      23546 07-9月 -21 06.32.42.000000 下午
       568 QIITGBC                                          41      45708 07-9月 -21 06.32.42.000000 下午
       569 OTSCIZLDWMJZFV                                   32      28990 07-9月 -21 06.32.42.000000 下午
       570 KQZFWBK                                          64      45629 07-9月 -21 06.32.42.000000 下午

已选择10行。

 

Sample2：

with cityOldestWomen as (
select id,name,gender,cityname,birthymd from (
select 
id,name,gender,cityname,to_char(birthday,'yyyy-MM-dd') as birthymd, 
rank() over (partition by cityname order by birthday) as seq
from customer
where gender='f')
where seq=1
order by id)
select * from cityOldestWomen

Result：

SQL> with cityOldestWomen as (
  2  select id,name,gender,cityname,birthymd from (
  3  select
  4  id,name,gender,cityname,to_char(birthday,'yyyy-MM-dd') as birthymd,
  5  rank() over (partition by cityname order by birthday) as seq
  6  from customer
  7  where gender='f')
  8  where seq=1
  9  order by id)
 10  select * from cityOldestWomen;

        ID NAME                                     G CITYNAME                                 BIRTHYMD
---------- ---------------------------------------- - ---------------------------------------- ----------
         1 XGZLORILKOGWELLJI                        f 山海关                                   1970-01-02
         2 JFMOXKKFDJPSNBNQS                        f 绥中                                     1970-01-03
         3 WKMIILRQAKY                              f 北戴河                                   1970-01-04
        23 MKSREQXPKJTWSK                           f 兴城                                     1970-01-24
        30 ILXYETNXOKXSB                            f 津滨                                     1970-01-31

SQL>

END

 

2022年1月7日增补

多个with的写法是以逗号分隔，第一个有with，其后都没有。

例子SQL：

with t1 as (select * from emp7 where id=1),
     t2 as (select * from emp7 where id=2)
select * from t1,t2

执行结果：

SQL> with t1 as (select * from emp7 where id=1),
  2       t2 as (select * from emp7 where id=2)
  3  select * from t1,t2
  4  ;

        ID NAME                         ID NAME
---------- -------------------- ---------- --------------------
         1 Andy                          2 Bill

END