oracle 基本语法 (2)

1.条件判断语句
CASE 
WHEN （条件1）
THEN  (条件成立返回什么1)
WHEN （条件2）
THEN  (条件成立返回什么2)
ELSE  (不成立返回什么)
END;


2.判断
DECODE(条件值,'值1','返回值1','值2','返回值2',...,'缺省值');
条件值 = 值1 就返回值1，=值2 就返回值2，都不等于就返回缺省值。


3.ROWNUM (伪列)


4.row_number() over (order by col desc) (伪列)
例：SELECT xfbh, row_number() OVER(ORDER BY col)  FROM T_HJXF_HJD_JBXX
产生伪列并按col字段排序


5.GROUPING(值)   （使用在group by中）
如果'值'为空就返回1，不为空就返回0。


6.DISTINCT（这个关键字来过滤掉多余的重复记录只保留一条）
例：select distinct dw from T_OA_TXLXX (过滤掉dw的字段的重复记录)


7.UNION (ALL) 连接 (并集) (相同的表结构才能连接)
UNION ALL 连接全部包括重复的记录
UNION 连接不包括重复的记录
例：
SELECT * FROM T_HJXF_HJD_JBXX
UNION ALL
SELECT * FROM T_HJXF_HJD_JBXX


8.intersect 表取交集 (交集)
SELECT * FROM T_HJXF_HJD_JBXX
INTERSECT
SELECT * FROM T_HJXF_HJD_JBXX


9.minus 表取差集 （差集）
SELECT * FROM T_HJXF_HJD_JBXX
MINUS
SELECT * FROM T_HJXF_HJD_JBXX

10.NVL(a,b) 如果a为空就返回b，如果不为空就返回a
SELECT NVL('AA','BB')  FROM DUAL;

11.group by (rollup|cube)
rollup 上钻，如rollup(a,b)的结果集相当于以下三部分的合集：group by a，group by a,b，不加group by子句的组合函数结果。
cube立方体，如cube(a,b) 的结果相当于a，b全部可能组合group by结果合集：group by a,group by b,group by ab,不加group by子句的组合函数结果。


举例：
SQL> select * from t1;
         A          B      VALUE 
---------- ---------- ---------- 
       111          1       9000 
       123          2       7000 
       111          2       1000 
       122          3       2000 
       123          5       3000 
       135          3       4000 
6 rows selected.
SQL> select a,b,sum(value) from t1 group by rollup(a,b);
         A          B SUM(VALUE)
---------- ---------- ----------
       111          1       9000
       111          2       1000
       111                 10000
       122          3       2000
       122                  2000
       123          2       7000
       123          5       3000
       123                 10000
       135          3       4000
       135                  4000
                           26000
11 rows selected.
SQL> select a,b,sum(value) from t1 group by cube(a,b);
         A          B SUM(VALUE)
---------- ---------- ----------
                           26000
                    1       9000
                    2       8000
                    3       6000
                    5       3000
       111                 10000
       111          1       9000
       111          2       1000
       122                  2000
       122          3       2000
       123                 10000
       123          2       7000
       123          5       3000
       135                  4000
       135          3       4000
15 rows selected.




12.ADD_MONTHS(time,months)函数可以得到某一时间之前或之后n个月的时间
例: select add_months(sysdate,-6) from dual; 该查询的结果是当前时间半年前的时间
select add_months(sysdate,6) from dual; 该查询的结果是当前时间半年后的时间

13.with as 用法
其实就是把一大堆重复用到的SQL语句放在with as 里面，取一个别名，后面的查询就可以用它
这样对于大批量的SQL语句起到一个优化的作用，而且清楚明了
例：
sql1 as (select to_char(a) s_name from test_tempa),
sql2 as (select to_char(b) s_name from test_tempb where not exists (select s_name from sql1 where rownum=1))
select * from sql1
union all
select * from sql2
union all
select 'no records' from dual
where not exists (select s_name from sql1 where rownum=1)
and not exists (select s_name from sql2 where rownum=1);
例：
with a as (select * from test)
select * from a;