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

Great Sites on MySQL

Percona

MySQL Performance Blog

Severalnines

推荐管理工具

Sequel Pro

phpMyAdmin

推荐书目

MySQL Cookbook

MySQL 相关项目

MariaDB

Drizzle

参考文档

http://mysql-python.sourceforge.net/MySQLdb.html

This topic created in 1320 days ago, the information mentioned may be changed or developed.

有一个 oracle 的 sql 要转写成 mysql 5.7 的写法，但是有几个分析函数很麻烦：

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 请问有什么快捷的写法么？

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

darnurash

OP/div>

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 都不如的，我觉得应该换了