04基本语法

基础语法

分页

采用伪列rownum

rownum 不支持 大于，between

查询前10条记录

select * from t_user t where ROWNUM <10;

按照学生ID排名，抓取前三条记录

SELECT * FROM(SELECT id,realname FROM T_USER ORDER BY id asc ) WHERE ROWNUM <=3

从第10条记录开始，提取10条记录

SELECT * FROM (SELECT ROWNUM rn,id,realname FROM (SELECT id,realname FROM T_USER)WHERE ROWNUM<=20) t2 WHERE T2.rn >=10;

按照学生ID排名，从第10条记录开始，提取10条记录

SELECT * FROM (SELECT ROWNUM rn,id,realname FROM (SELECT id,realname FROM T_USER ORDER BY id asc)WHERE ROWNUM<=20) t2 WHERE T2.rn >=10;

多表查询

如果在查询的时候，直接从多个表中获取数据。没有添加条件判断，会出现笛卡尔积

笛卡尔集会在下面条件下产生

• 省略连接条件
• 连接条件无效
• 所有表中的所有行互相连接

为了避免笛卡尔集， 可以在 WHERE 加入有效的连接条件

等值查询

SELECT employees.employee_id, employees.last_name, 
       employees.department_id, departments.department_id,
       departments.location_id
FROM   employees, departments
WHERE  employees.department_id = departments.department_id;

非等职查询

-- EMPLOYEES表中的列工资应在JOB_GRADES表中的最高工资与最低工资之间
SELECT e.last_name, e.salary, j.grade_level
FROM   employees e, job_grades j
WHERE  e.salary BETWEEN j.lowest_sal AND j.highest_sal;

内连接

SELECT e.last_name, d.department_name 
FROM employees e, departments d
WHERE e.department_id = d.department_id

-- SQL1999语法的方式
SELECT e.last_name, d.department_name 
FROM employees e 
INNER JOIN departments d ON e.department_id = d.department_id

左外连接

SELECT e.last_name, d.department_name 
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)

-- SQL1999方式
SELECT e.last_name, d.department_name 
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) 

右外连接

SELECT e.last_name, d.department_name 
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id

-- SQL1999方式
SELECT e.last_name, d.department_name 
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) 

满外连接

-- 满外连接
SELECT e.last_name, e.department_id, d.department_name
FROM   employees e
FULL OUTER JOIN departments d
ON   (e.department_id = d.department_id)

窗口函数

function(arg1,arg2,...argN)
over([partition-by-clause] [order-by-clause] [windowing-clause])

[rows | range] between <start expr> and <end expr>

<start expr> is 
[unbounded preceding | current row | n preceding | n following]

<end expr> is 
[unbounded following | current row | n preceding | n following]

1. 常用聚合函数
2. LAG(expr, <offset>, <default>) 访问之前的行,OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如第一行不存在之前行)
3. LEAD(expr, <offset>, <default>) 访问之后的行,OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如最后行不存在之前行)
4. FIRST_VALUE一个组的第一个值
5. LAST_VALUE一个组的最后一个值
6. ROW_NUMBER() 为每条记录返回一个数字
7. RANK()返回数据项在分组中的排名，排名相等会在名次中留下空位，跳跃排序
8. DENSE_RANK()返回数据项在分组中的排名，排名相等会在名次中不会留下空位,连续排序
9. RATIO_TO_REPORT()括号中就是分子，OVER() 括号中就是分母
10. ntile(n) 对同一个数据分区中结果划分为n份，返回划分结果下标，1开始

CUME_DIST()

返回特定值对于一组值的位置“cumulative distribution”(累积分布) ，累积分布,即行在组中的相对位置,返回0 ~ 1

假如有10个数据 第一个为0.1，第二个为0.2 …….第10个为1

相同order，结果相同，跳序

PERCENT_RANK()

返回某个值相对于一组值的百分比排名，类似CUME_DIST, 1/(行的序数 - 1)

加入有10个数据，第一个为 (1-1)/(10-1),第二个为(2-1)/(10-1),第10个为(10-1)/(10-1)

相同order，结果相同，跳序

PERCENTILE_CONT

输入一个百分比（该百分比就是按照PERCENT_RANK函数计算的值），返回该百分比位置的平均值

PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY COURSE_SCORE) OVER(PARTITION BY CLASS_NAME)

PERCENTILE_DISC

返回一个与输入的分布百分比值相对应的数据值，分布百分比的计算方法见函数CUME_DIST，如果没有正好对应的数据值，就取大于该分布值的下一个值。

PERCENTILE_DISC(0.6) WITHIN GROUP(ORDER BY COURSE_SCORE) OVER(PARTITION BY CLASS_NAME)