mysql - 具有两列并按第三列排序的 Mysql rank 函数
问题描述
这是我在 Sql Server 中使用的代码,我想在 Mysql 中使用相同的代码
不幸的是,Mysql 5 没有排名功能。我试过谷歌仍然没有帮助按多列分区和按第三列排序
SELECT
A.ID, A.COL1, A.COL2, A.COL3
FROM (
SELECT
ID, COL1, COL2, COL3, ROW_NUMBER() OVER (PARTITION BY COL1, COL2 ORDER BY COL3 DESC,ID) AS RN
FROM #temp) A
WHERE A.RN=1
可以请帮助它在mysql中使用
SELECT * FROM MysqlTemp
CREATE TEMPORARY TABLE MysqlTemp
(
ID INT
, Col1 VARCHAR(20)
, Col2 VARCHAR(20)
, Col3 VARCHAR(30)
)
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (1,'Hi','Hi','A21');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (2,'Hi','Hi','A21');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (3,'Hello','Hello');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (4,'Hello','Hello');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (5,'Hey','Hey');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (6,'Hey','Hey','B45');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (7,'Howdy','Howdy','V44');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (8,'Howdy','Howdy');
预期值为 1 , 3 , 6 , 7
解决方案
我冒昧地添加了更多数据以确保我满足所有测试用例。
CREATE TABLE MysqlTemp
(
ID INT
, Col1 VARCHAR(20)
, Col2 VARCHAR(20)
, Col3 VARCHAR(30)
) ;
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (1,'Hi','Hi','A21');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (2,'Hi','Hi','A21');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (3,'Hello','Hello');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (4,'Hello','Hello');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (5,'Hey','Hey');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (6,'Hey','Hey','B45');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (7,'Howdy','Howdy','V44');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (8,'Howdy','Howdy');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (9,'Howdy','Howdy');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (10,'Howdy','Howdy','V45');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (11,'ROWDY','ROWDY','X45');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (12,'ROWDY','ROWDY');
INSERT INTO MysqlTemp (ID,Col1,Col2) VALUES (13,'ROWDY','ROWDY');
INSERT INTO MysqlTemp (ID,Col1,Col2,Col3) VALUES (14,'ROWDY','ROWDY','X44');
select m1.*,
(select count(1) from MysqlTemp m2
where coalesce(col3,'Z') <= coalesce(m1.col3,'Z')
and (coalesce(col3,'Z') < coalesce(m1.col3,'Z') or id <= m1.id)
and col1 = m1.col1
and col2 = m1.col2
) as rnk
from MysqlTemp m1
ID Col1 Col2 Col3 rnk
1 Hi Hi A21 1
2 Hi Hi A21 2
3 Hello Hello (null) 1
4 Hello Hello (null) 2
5 Hey Hey (null) 2
6 Hey Hey B45 1
7 Howdy Howdy V44 1
8 Howdy Howdy (null) 3
9 Howdy Howdy (null) 4
10 Howdy Howdy V45 2
11 ROWDY ROWDY X45 2
12 ROWDY ROWDY (null) 3
13 ROWDY ROWDY (null) 4
14 ROWDY ROWDY X44 1
说明:在 mysql 中,生成排名的一种方法是使用相关子查询。
这部分针对查询中的每条记录执行
select count(1) from MysqlTemp m2
where coalesce(col3,'Z') <= coalesce(m1.col3,'Z')
and (coalesce(col3,'Z') < coalesce(m1.col3,'Z') or id <= m1.id)
and col1 = m1.col1
and col2 = m1.col2
这部分确保连接只处理匹配此条件的记录。基本上,这是分区子句。
and col1 = m1.col1
and col2 = m1.col2
这部分可能很简单<=,因为你有空值,你可以达到的最大值用于在空值时进行比较。
where coalesce(col3,'Z') <= coalesce(m1.col3,'Z')
到目前为止,我们正在尝试计算同一分区内所有小于 col3 的记录。第一条记录将只有一条小于或 = 的记录。第二条记录将有 2 条记录小于或 =。很快。这就是排名。
以下部分是 order by 子句的第二部分。
and (coalesce(col3,'Z') < coalesce(m1.col3,'Z') or id <= m1.id)
为什么这里有一个额外的或条件?因为 id > 对于第二和第三条记录,你会错过需要统计的第一条记录。所以一个或。
我知道它会有点模糊。分别尝试每个部分的查询,您就会明白。
推荐阅读
- jquery - 如何在表单提交时以编程方式覆盖 select2 css?
- c# - 如何使用 MongoDB C# 更新子项内部的整数字段
- r - 是否有像 rollapplyr 这样的快速 R 函数随着窗口大小的增加?
- javascript - 我希望从另一个下拉选择中显示和隐藏 1 或 2 个下拉菜单
- html - 将注销操作放入标签中
- jquery - 如何删除进度条上的黑条(Chrome 故障)?
- javascript - 如何更新 JSON 数据
- python - Keras 的有状态 LSTM 中的一批样本之间的状态是否保留?
- c++ - 有没有办法将 EGL 绘制到 /dev/fb1 而不是 /dev/fb0 而无需在 Raspberry Pi 上复制数据?
- c++ - 我需要帮助在我的循环中获得新的平均值