Oracle SQL精妙SQL语句讲解

-- soft parse
--java中的同等语句是 PreparedStatement.execute()
sql_1 := 'insert into t_hard(id) values(:id)';
FOR i IN 1 .. 1000 LOOP
EXECUTE IMMEDIATE sql_1
USING i;
END LOOP;
COMMIT;
END;

-- 正确的分页算法
SELECT *
FROM (SELECT a.*, ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= 500)
WHERE rn > 480 ;

-- 分页算法(why not this one)
SELECT a.*, ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= 500 AND ROWNUM > 480;

-- 分页算法(why not this one)
SELECT b.*
FROM (SELECT a.*, ROWNUM rn
FROM t_employees a
WHERE ROWNUM < = 500
ORDER BY first_name) b
WHERE b.rn > 480;

-- OLAP
-- 小计合计
SELECT CASE
WHEN a.deptno IS NULL THEN
'合计'
WHEN a.deptno IS NOT NULL AND a.empno IS NULL THEN
'小计'
ELSE
'' a.deptno
END deptno,
a.empno,
a.ename,
SUM(a.sal) total_sal
FROM scott.emp a
GROUP BY GROUPING SETS((a.deptno),(a.deptno, a.empno, a.ename),());

-- 分组排序
SELECT a.deptno,
a.empno,
a.ename,
a.sal,
-- 可跳跃的rank
rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r1,
-- 密集型rank
dense_rank() over(PARTITION BY a.deptno ORDER BY a.sal DESC) r2,
-- 不分组排序
rank() over(ORDER BY sal DESC) r3
FROM scott.emp a
ORDER BY a.deptno,a.sal DESC;

-- 当前行数据和前/后n行的数据比较
SELECT a.empno,
a.ename,
a.sal,
-- 上面一行
lag(a.sal) over(ORDER BY a.sal DESC) lag_1,
-- 下面三行
lead(a.sal, 3) over(ORDER BY a.sal DESC) lead_3
FROM scott.emp a
ORDER BY a.sal DESC;

（出处：清风学院）