本文共 2397 字，大约阅读时间需要 7 分钟。

SQL???OVER???????

?SQL???????ORDER BY??OVER???????????????????????????????OVER?????????????????????????????????????????????????OVER?????????????

1.1 ?????ORDER BY?????

?SQL??ORDER BY?????????????????????????????????????????

SELECT column1, column2FROM tableORDER BY column1 ASC, column2 DESC;

ORDER BY???????????????????????????ASC?DESC???????

a) ???????

SELECT ename, salFROM empWHERE deptno = 10ORDER BY sal ASC;

b) ???????

SELECT ename, sal, deptnoFROM empWHERE deptno = 10ORDER BY deptno ASC, sal DESC;

?????????????????????????????????????????????????

1.2 ????????????

??????????????????PARTITION BY????????????????????????????PARTITION BY????????????????????????

?????????

SELECT column1, column2FROM tablePARTITION BY columnA, columnBORDER BY columnC ASC;

?????????

??????????????????????????????????????????????????????????

SELECT deptno, last_value(sal) over(partition by deptno order by sal) as max_salFROM empGROUP BY deptno;

??????GROUP BY?OVER??????????????????????

1.3 OVER?????????

OVER?????SELECT????????????????????????????????????????????????

OVER???????

SELECT column1, column2FROM tableWHERE conditionOVER (    partition by columnA, columnB    order by columnC ASC);

OVER?????????

SELECT deptno, empno, ename, sal, last_value(sal) over(partition by deptno) as max_salFROM empWHERE deptno = 30;

2. ?????

SELECT deptno, empno, ename, sal, min(sal) over(partition by deptno) as min_salFROM empWHERE deptno = 30;

3. ???????

SELECT deptno, empno, ename, sal, last_value(sal) over(partition by deptno order by sal desc) as max_salFROM empWHERE deptno = 30ORDER BY mgr desc;

---

2. OVER???????

???????OVER???????????????????????

4. ??????

   OVER?????????????????????????????????

5. ???????

   ???OVER???????????????????????????

6. ??????????

   OVER?????GROUP BY?????????????????????

---

3. OVER???????

???OVER???????????????

7. ???????

   OVER????????ORDER BY??????????????????????????

8. ??????

   OVER?????????????????????????????

9. ????

   ?????OVER????????????????????

---

4. OVER???????

??1?????????

SELECT deptno, last_value(sal) over(partition by deptno order by sal) as max_salFROM empGROUP BY deptno;

??2????????????

SELECT deptno, empno, ename, sal, last_value(sal) over(partition by deptno order by sal desc, empno asc) as max_salFROM empWHERE deptno = 30;

??3???????

SELECT deptno, min(sal) over(partition by deptno) as min_salFROM empGROUP BY deptno;

---

???????????OVER??????????????????????????????????????SQL?????????