无法理解如何强制使用row_num()而不通过朴素的sql方法使用它，而秩和dense_rank工作

Question

我有以下数据：

mysql>按国家从covid_test订单中选择*确认；

+------+---------+-----------+
| 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 |
+------+---------+-----------+

下面的排名和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？

Answer 1

还必须检查条件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;

见演示。