印象 ● sql函数

with as:

例：

insert into PHA_CODESET.HCPCS(           --插入表
SID,
CD,
BASEDT,
DESCRIPTION,
DESCRIPTIONSHORT,
VERSION,
EFFECTIVEDT
)
With a as (select *, row_number() over (partition by cd order by version desc) as rn from PHA_CODESET._RAWHCPCS)            --with as: 使用a作为一个公用表达式，该表达式在使用上与表变量类似。
select row_number () over (order by a.cd) + (select coalesce(max(SId), 0) from PHA_CODESET.HCPCS)                                                   CTE后面直接跟SQL语句（select, insert, update），否则失效。
,trim(a.cd)
,TO_DATE(substring(a.version,3,4), ‘YYYY‘)
,trim(a.LongDescription)
,trim(a.ShortDescription)
,trim(a.version)
,TO_DATE(substring(a.version,3,4), ‘YYYY‘)
from a
where a.rn = 1
order by a.CD;

印象 ● sql函数

原文：http://www.cnblogs.com/morongjieshao/p/4767172.html