oracle 重命名列

今天使用oracle数据库重命名列后发现新的列表无法在where条件下使用：

select count(1) from (select To_Number(Extract(hour from checktime)) 
AS CHECK_HOUR 
from sams_checkinout where CHECK_HOUR >= To_Number(22))

查询了一番发现oracle的确是不支持这样操作的，可以把查询出来的数据重命名为一张表，再查询表内的数据

with e as (select To_Number(Extract(hour from checktime)) 
AS CHECK_HOUR from sams_checkinout) 
select count(1) from e where CHECK_HOUR >= To_Number(22); 

with as的语法：

--针对一个别名
with tmp as (select * from tb_name)

--针对多个别名
with
   tmp as (select * from tb_name),
   tmp2 as (select * from tb_name2),
   tmp3 as (select * from tb_name3),
   …

将重复使用的SQL语句放到一张临时表中，效率较高。

特别对于union all比较有用。（UNION 操作符用于合并两个或多个 SELECT 语句的结果集，UNION ALL 命令会列出所有的值）

因为union all的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用with as短语，则只要执行一遍即可。

with
    sql1 as (select to_char(a) s_name from test_tempa),
    sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from sql1
union all
select * from sql2
union all
select 'no records' from dual
       where not exists (select s_name from sql1 where rownum=1)
       and not exists (select s_name from sql2 where rownum=1);