时间戳

首页 > 解决方案 > 无法理解如何在不通过幼稚的 sql 方法使用 row_num() 的情况下强加它，而 rank 和 dense_rank 有效

mysql - 无法理解如何在不通过幼稚的 sql 方法使用 row_num() 的情况下强加它，而 rank 和 dense_rank 有效

问题描述

我有以下数据集：

mysql> select * from covid_test order by Country, Confirmed;

+------+---------+-----------+
| SNO  | Country | Confirmed |
+------+---------+-----------+
|   19 | China   |         0 |
|    1 | China   |         1 |
|    7 | China   |         2 |
|    9 | China   |         4 |
|   78 | China   |         4 |
|   12 | China   |         5 |
|    3 | China   |         6 |
|   26 | China   |         9 |
|   35 | China   |        10 |
|    2 | China   |        14 |
|    6 | China   |        26 |
|   14 | China   |       444 |
|   77 | India   |        15 |
+------+---------+-----------+

下面对 rank 和 dense_rank 的查询工作正常，但是我无法查询 row_num，我的所有策略都失败了：

mysql> select ct.*, (select count(distinct Confirmed)+1  from covid_test ct1 where ct1.Country = ct.Country and ct1.Confirmed < ct.Confirmed and ct1.SNO <> ct.SNO) as densernk from covid_test ct order by Country, Confirmed;
+------+---------+-----------+--------+
| SNO  | Country | Confirmed | densernk |
+------+---------+-----------+--------+
|   19 | China   |         0 |      1 |
|    1 | China   |         1 |      2 |
|    7 | China   |         2 |      3 |
|    9 | China   |         4 |      4 |
|   78 | China   |         4 |      4 |
|   12 | China   |         5 |      5 |
|    3 | China   |         6 |      6 |
|   26 | China   |         9 |      7 |
|   35 | China   |        10 |      8 |
|    2 | China   |        14 |      9 |
|    6 | China   |        26 |     10 |
|   14 | China   |       444 |     11 |
|   77 | India   |        15 |      1 |


mysql> select ct.*, (select count(distinct SNO) from covid_test ct1 where ct1.Country = ct.Country and ct1.Confirmed < ct.Confirmed and ct1.SNO <> ct.SNO) as rnk from covid_test ct order by Country, Confirmed;
+------+---------+-----------+------+
| SNO  | Country | Confirmed | rnk  |
+------+---------+-----------+------+
|   19 | China   |         0 |    0 |
|    1 | China   |         1 |    1 |
|    7 | China   |         2 |    2 |
|    9 | China   |         4 |    3 |
|   78 | China   |         4 |    3 |
|   12 | China   |         5 |    5 |
|    3 | China   |         6 |    6 |
|   26 | China   |         9 |    7 |
|   35 | China   |        10 |    8 |
|    2 | China   |        14 |    9 |
|    6 | China   |        26 |   10 |
|   14 | China   |       444 |   11 |
|   77 | India   |        15 |    0 |
+------+---------+-----------+------+

如何为同一组实现 row_num？

标签： mysqlsqlrow-number

解决方案

您还必须检查条件ct1.Confirmed = ct.Confirmed，在这种情况下，您必须计算行数ct1.SNO < ct.SNO：

select ct.*, 
  (
    select count(*) + 1  
    from covid_test ct1 
    where ct1.Country = ct.Country 
      and (
        ct1.Confirmed < ct.Confirmed 
        or (ct1.Confirmed = ct.Confirmed AND ct1.SNO < ct.SNO)
      )  
  ) as row_num 
from covid_test ct 
order by Country, Confirmed;

请参阅演示。

推荐阅读