MySQL 中的窗口函数 ROWNUM 实现

本贴最后更新于 1302 天前,其中的信息可能已经时移世异

MySQL 8.0 后开始有窗口函数的。

窗口函数是作用域于每条语句上的,会返回相同数据量的行数。聚合函数会根据计算结果返回一个值。

窗口函数的一般格式:

function_name()over(write SQL) 

按照功能划分,可以把 MySQL 支持的窗口函数分为如下几类:

  • 序号函数:row_number() / rank() / dense_rank()
  • 分布函数:percent_rank() / cume_dist()
  • 前后函数:lag() / lead()
  • 头尾函数:first_val() / last_val()
  • 其他函数:nth_value() / nfile()

题目:

-- 查询 emp 表前5条记录
SELECT * FROM emp WHERE rownum <= 5; -- Oracle 中可行。 MySQL 中语法错误 Unknown column 'rownum' in 'where clause'
SELECT * FROM emp;
SELECT * FROM emp e, (SELECT ROW_NUMBER() OVER() AS rowno, empno FROM emp) t WHERE t.rowno <= 5 AND e.empno = t.empno;
-- 查询工资最高的5名员工的信息
SELECT *, t.rowno FROM emp e, (SELECT row_number() OVER(ORDER BY sal DESC) AS rowno, empno FROM emp ) t WHERE t.rowno <= 5 AND e.empno = t.empno ORDER BY e.sal DESC;
SELECT * FROM emp ORDER BY sal DESC;

-- 查询第3-5条记录,无需排序
SELECT * FROM emp;
SELECT * FROM emp e JOIN (SELECT ROW_NUMBER() OVER() AS rowno, empno FROM emp) t ON t.empno = e.empno WHERE t.rowno >= 3 AND t.rowno <=5;
SELECT * FROM emp e JOIN (SELECT ROW_NUMBER() OVER() AS rowno, empno FROM emp) t ON e.empno = t.empno WHERE t.rowno BETWEEN 3 AND 5; -- between:在两值之间,	>=min&&<=max. AND:逻辑与。&:位运算,按位与。不能使用&。
select * from (select rownum num, e.* from emp e) where num >= 3 and num <= 5; -- Oracle 中用法

参考文章:
MySQL 中的窗口函数
MySQL 8.0 窗口函数

  • SQL
    124 引用 • 296 回帖 • 3 关注
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    673 引用 • 535 回帖
  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    330 引用 • 612 回帖
  • 笔记

    好记性不如烂笔头。

    303 引用 • 777 回帖
2 操作
PeterChu 在 2020-09-03 21:38:09 更新了该帖
PeterChu 在 2020-09-03 21:35:58 更新了该帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...
  • PeterChu
    作者

    静态站点可以回复,但是不能再次编辑回复吗?

    当再次编辑之前的回复时(比如编辑上一条回复),会报错:

    Access to XMLHttpRequest at 'https://hacpai.com/apis/vcomment/1599244254993' from origin 'https://txjchu.gitee.io' has been blocked by CORS policy: No 'Access-Control-Allow-Origin' header is present on the requested resource.
    
    PUT https://hacpai.com/apis/vcomment/1599244254993 net::ERR_FAILED
    
  • 其他回帖
  • PeterChu
    作者

    分组排序:

    ROW_NUMBER 函数计算的值就表示每组内部排序后的顺序编号,组内连续且唯一。

    -- 使用 ROW_NUMBER 函数实现分组排序
    SELECT deptno, ename, empno, ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY empno) AS emp_id FROM emp; -- Oracle 中用法,MySQL 中同样可用
    SELECT deptno, ename, empno, ROW_NUMBER() OVER( ORDER BY empno) AS emp_id FROM emp; -- 若没有 partition by(分割) 则不会按照 deptno 进行分组计算 empno,而是整表所有数据只计算一个行号序列。