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

这是我在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

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

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

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

1条答案

相关问题

热门标签

最新问答