请教一个比较麻烦的 Oracle 转 Mysql 的问题

SELECT * FROM ( SELECT s.NAME , s.SUBJECT , s.SCORES , s.TERM , ROW_NUMBER() OVER (PARTITION BY SUBJECT ORDER BY SCORES DESC NULLS LAST ) AS rn, count(*) OVER (PARTITION BY SUBJECT ) AS rr, DENSE_RANK() OVER (PARTITION BY SUBJECT ORDER BY SCORES DESC) AS rd FROM SCORES s) FF WHERE FF.rn>2 AND FF.rr>2 AND FF.rd>2

 难点就是实现 ROW_NUMBER() ... NULLS LAST)、count(*) OVER...、DENSE_RANK() OVER
 请问有什么快捷的写法么？

subject

scores

Over

partition

3 replies • 2022-10-01 15:31:47 +08:00

darnurash

Oct 1, 2022

建表语句：
CREATE TABLE "SCORES"
( "NAME" VARCHAR2(100),
"SUBJECT" VARCHAR2(100),
"SCORES" NUMBER(*,0),
"TERM" NUMBER(*,0)
)

c6h6benzene

Oct 1, 2022

从 Stackoverflow 看到的：

SELECT
(@row_number := @row_number + 1) AS rnk, points
FROM yourTable,
(SELECT @row_number := 0) AS x
ORDER BY points DESC;

至于 DENSE_RANK 大概你得自己写 join 根据 row_number 来判断了（或者丢到前端去）。

wxf666

Oct 1, 2022

为啥不用 MySQL 8.0 呢？

这些窗口函数连 SQLite 都已经在 4 年多前支持了。连 SQLite 都不如的，我觉得应该换了