如何使用SQLite基于record_number和date列实现正确行排名？

需要给main_clinics表新增visit_rank列，排名规则如下：

• 当record_number和date同时相同时，两行排名一致
• 两者任一不同，排名不同

例如前两行record_number和date均相同，排名应为1；第三行record_number相同但date不同，排名应为2。但执行以下代码后，所有行的visit_rank都被设为1：

ALTER TABLE main_clinics
    ADD COLUMN visit_rank INTEGER;
UPDATE main_clinics
    SET
    visit_rank = (
        SELECT DENSE_RANK() OVER (PARTITION BY record_number ORDER BY date ASC)
        FROM main_clinics 
    );

问题原因 #

原更新语句中的子查询没有和主表做关联，它只是返回整个表的DENSE_RANK结果集，但数据库无法匹配到当前行，会默认取结果集的第一行值，导致所有行排名都为1。

正确解决方案 #

方案一：关联子查询

通过嵌套子查询先计算所有行的排名，再通过record_number和date匹配到当前行的排名值：

ALTER TABLE main_clinics ADD COLUMN visit_rank INTEGER;

UPDATE main_clinics mc
SET visit_rank = (
    SELECT dr.rank
    FROM (
        SELECT 
            record_number,
            date,
            DENSE_RANK() OVER (PARTITION BY record_number ORDER BY date ASC) AS rank
        FROM main_clinics
    ) dr
    WHERE dr.record_number = mc.record_number 
      AND dr.date = mc.date
);

方案二：CTE公共表表达式（可读性更强）

先通过CTE计算出所有行的正确排名，再关联主表更新：

ALTER TABLE main_clinics ADD COLUMN visit_rank INTEGER;

WITH ranked_clinics AS (
    SELECT 
        record_number,
        date,
        DENSE_RANK() OVER (PARTITION BY record_number ORDER BY date ASC) AS rank
    FROM main_clinics
)
UPDATE main_clinics mc
SET visit_rank = rc.rank
FROM ranked_clinics rc
WHERE mc.record_number = rc.record_number 
  AND mc.date = rc.date;

说明 #

• DENSE_RANK()函数会为同一record_number分组下，相同date的行分配相同排名，且排名连续（不会出现跳号），完全符合需求
• 必须同时用record_number和date作为关联条件，确保每行能获取到自身对应的排名值

内容的提问来源于stack exchange，提问作者Nemra Khalil