mysql rank函数,有两列,按第三列排序

vxbzzdmp  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(506)

这是我在sql server中使用的代码,我想在mysql中使用相同的代码
不幸的是mysql 5没有rank函数。我试过google,但它对按多列划分和按第三列排序没有帮助

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

bwitn5fc

bwitn5fc1#

我冒昧地添加了一些数据,以确保满足所有测试用例。

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>表示第二和第三条记录,您将错过需要计数的第一条记录。所以一个手术室。
我知道会有点模糊。对每个部分分别进行查询,您就会理解。

相关问题