with as oracle_11g

sql oracle mysql 数据库 基础 知识 窗口函数 lag与lead函数 LISTAGG函数 connect by 结构化查询 备份和恢复 mysql5.7 mysql8.0 oracle_11g docker

with as oracle_11g

使用With…As定义，以便提高Sql的查询效率和代码的简洁

-- with as 可以理解为一张临时表或者理解成sql片段，在多次查询语句相同的时候可以抽取出来，达到'一次解析，多次使用'
-- 如果每个部分都去执行一遍的话，则成本比较高，可以使用with as短语，则只要执行一遍即可
-- with as 后面必须紧接着查询语句

with temp as
 (select '10001' as province_code from dual)
 
select case
         when (select * from temp) = '10001' then
          'equals'
         when (select * from temp) = '10002' then
          'not equals'
         else
          'unknown'
       end is_equals
  from dual;