今天在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
结果为:
有些知识很久不用很容易忘记,特此记录。
全文完。