SQL子查询语法错误[156]：WHERE关键字附近语法不正确求助

你的SQL语法不正确，错误根源在于JOIN子查询TMP时缺少ON关联条件，SQL要求使用JOIN关联两个数据集必须通过ON子句定义匹配规则，否则数据库无法识别主表与子查询的关联逻辑，从而触发语法错误。此外，你试图通过WHERE TMP.row >10过滤行号，但因缺少关联条件，逻辑本身也不成立。

修正后的SQL示例（两种方案） #

方案1：使用CTE实现分页

WITH PatientRow AS (
    SELECT 
        PatientId,
        ROW_NUMBER() OVER(ORDER BY PatientId) AS [ROW]
    FROM [HIS].Patient.Patient
)
SELECT TOP(100)
    '' AS [id],
    p.PatientId AS [patient_id],
    po.MrNo AS [local_mr_no],
    o.Code AS [organization_code],
    o.Name AS [organization_name],
    p.RegistrationDate AS [registration_date],
    p.PatientStatusId AS [status_id],
    ps.Name AS [status],
    '' AS [created_at],
    '' AS [updated_at],
    '' AS [deleted_at]
FROM
    [HIS].Patient.Patient p
JOIN
    [HIS].Patient.PatientStatus ps ON p.PatientStatusId = ps.PatientStatusId
JOIN
    [HIS].Patient.PatientOrganization po ON p.PatientId = po.PatientId
JOIN
    [HIS].[System].Organization o ON po.OrganizationId = o.OrganizationId
JOIN
    PatientRow pr ON p.PatientId = pr.PatientId
WHERE 
    pr.[ROW] > 10;

方案2：直接在JOIN子查询中关联主表

SELECT TOP(100)
    '' AS [id],
    p.PatientId AS [patient_id],
    po.MrNo AS [local_mr_no],
    o.Code AS [organization_code],
    o.Name AS [organization_name],
    p.RegistrationDate AS [registration_date],
    p.PatientStatusId AS [status_id],
    ps.Name AS [status],
    '' AS [created_at],
    '' AS [updated_at],
    '' AS [deleted_at]
FROM
    [HIS].Patient.Patient p
JOIN
    [HIS].Patient.PatientStatus ps ON p.PatientStatusId = ps.PatientStatusId
JOIN
    [HIS].Patient.PatientOrganization po ON p.PatientId = po.PatientId
JOIN
    [HIS].[System].Organization o ON po.OrganizationId = o.OrganizationId
JOIN
    (SELECT
         PatientId,
         ROW_NUMBER() OVER(ORDER BY PatientId) AS [ROW]
     FROM 
         [HIS].Patient.Patient) AS TMP ON p.PatientId = TMP.PatientId
WHERE 
    TMP.[ROW] > 10;

关键说明 #

• 两种方案都先为每个PatientId计算行号，再通过PatientId关联主表，确保行号与主表记录一一对应。
• ROW是SQL保留字，用方括号[ROW]包裹可避免语法冲突。
• 最终实现跳过前10条数据，获取接下来的100条记录的分页逻辑。

内容的提问来源于stack exchange，提问作者My August