时间戳

首页 > 解决方案 > 在 where 子句中无法访问按变量分区

sql-server - 在 where 子句中无法访问按变量分区

问题描述

SELECT DISTINCT
    cs.file_id,  
    null, 
    'lol', 
    t.encounter_id, 
    e.pan, e.mr, 
    e.provider_id, 
    t.c_date_of_service, 
    GETDATE(),
    ROW_NUMBER() OVER (PARTITION BY e.encounter_id ORDER BY e.encounter_id DESC) row
FROM 
    encounters e WITH (NOLOCK)
JOIN 
    dummy ec ON ec.encounter_id = e.encounter_id
JOIN 
    case_status cs ON cs.case_id = ec.case_id
JOIN 
    (SELECT DISTINCT
         e.encounter_id, c.date_of_service AS c_date_of_service
     FROM 
         encounters e WITH (NOLOCK)
     JOIN 
         dummy ec ON ec.encounter_id = e.encounter_id
     JOIN 
         cases c ON ec.case_id = c.case_id
     GROUP BY
         e.pid, c.date_of_service, e.encounter_id
     HAVING
         (COUNT(0) > 1 AND e.pid IS NOT NULL)) AS t ON t.encounter_id = e.encounter_id
WHERE
    row = 1

我收到一个错误

列名“行”无效

如果我只是删除where row = 1它在 SQL Server 中工作正常。

还有一件事 -ROW_NUMBER() OVER (PARTITION BY e.encounter_id ORDER BY e.encounter_id DESC)显示不同的行号，在结果数据RANK() OVER (PARTITION BY e.encounter_id ORDER BY e.encounter_id DESC)中有重复的地方显示所有排名为 1 。encounter_id

标签： sql-serverdense-rankpartition-by

解决方案

您不能直接在 where 子句中使用计算列，您需要将其包装在内联查询中，然后在其上添加 where 子句。

select * from (SELECT DISTINCT
        cs.file_id,  
        null, 
        'lol', 
        t.encounter_id, 
        e.pan, e.mr, 
        e.provider_id, 
        t.c_date_of_service, 
        GETDATE(),
        ROW_NUMBER() OVER (PARTITION BY e.encounter_id ORDER BY e.encounter_id DESC) row
    FROM 
        encounters e WITH (NOLOCK)
    JOIN 
        dummy ec ON ec.encounter_id = e.encounter_id
    JOIN 
        case_status cs ON cs.case_id = ec.case_id
    JOIN 
        (SELECT DISTINCT
             e.encounter_id, c.date_of_service AS c_date_of_service
         FROM 
             encounters e WITH (NOLOCK)
         JOIN 
             dummy ec ON ec.encounter_id = e.encounter_id
         JOIN 
             cases c ON ec.case_id = c.case_id
         GROUP BY
             e.pid, c.date_of_service, e.encounter_id
         HAVING
                 (COUNT(0) > 1 AND e.pid IS NOT NULL)) AS t ON t.encounter_id = 
                          e.encounter_id

        ) as A WHERE A.row = 1

推荐阅读