[简单]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

    结果为：

   

 

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

      全文完。