oracle中with使用union,[简单]oracle with中union all使用简单示例

今天在plsql中使用with时候用了union。sql如下：

with a as

(select to_date('2014-01-02', 'yyyy-mm-dd') startDate, 2 v_num from dual union

select to_date('2014-01-06', 'yyyy-mm-dd') startDate, 1 v_num from dual union

select to_date('2014-01-06', 'yyyy-mm-dd') startDate, 2 v_num from dual union

select to_date('2014-01-06', 'yyyy-mm-dd') startDate, 1 v_num from dual union

select to_date('2014-01-06', 'yyyy-mm-dd') startDate, 2 v_num from dual)

select count(1),startDate from a group by startDate

结果如下：

看到这个结果很惊讶，而是使用

select * from a order by startDate,v_num

看了下，结果为：

一开始还以为是with过滤掉了重复值，后来想到自己用错了，如果先保留重复值，不能使用union,必须使用union all.如下：

with a as

(select to_date('2014-01-02', 'yyyy-mm-dd') startDate, 2 v_num from dual union all

select to_date('2014-01-06', 'yyyy-mm-dd') startDate, 1 v_num from dual union all

select to_date('2014-01-06', 'yyyy-mm-dd') startDate, 2 v_num from dual union all

select to_date('2014-01-06', 'yyyy-mm-dd') startDate, 1 v_num from dual union all

select to_date('2014-01-06', 'yyyy-mm-dd') startDate, 2 v_num from dual)

select * from a order by startDate,v_num

结果为：

有些知识很久不用很容易忘记，特此记录。

全文完。