oracle "with as" "extract"-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_19601/article/details/82071252

本文介绍了如何使用WITH AS子句来优化SQL查询，并详细解释了EXTRACT函数的应用场景及正确使用方法。通过实例展示了如何从日期时间中抽取年、月、日等信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

with a as (select * from test)

select * from a;


其实就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它

这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了


EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression.
The field you are extracting must be a field of the datetime_value_expr or interval_value_expr. For example, you can extract only YEAR, MONTH, and DAY from a DATE value. Likewise, you can extract TIMEZONE_HOUR and TIMEZONE_MINUTE only from the TIMESTAMP WITH TIME ZONE datatype.
eg.


SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL; return Current Year
SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL; return Current Month
SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL; return Current Day
SELECT EXTRACT(HOUR FROM SYSDATE) FROM DUAL;Error
       Correct:SELECT EXTRACT(HOUR FROM TIMESTAMP '2005-10-10 10:10:10') FROM DUAL;

SELECT EXTRACT(MINUTE FROM SYSDATE) FROM DUAL;Error
       Correct:SELECT EXTRACT(MINUTE FROM TIMESTAMP '2005-10-10 10:10:10') FROM DUAL;