sql - 当所有字段都不同时，如何忽略返回的 MS Access 查询中的一行

Question

我正在尝试在 Access 2010 上创建一个查询，该查询只为每位患者生成一行。患者数量非常少（每个患者由

表 n) 中的唯一 nhs_number 被列为在表 pp 中有 2 个实践，因此为他们生成了两行。有没有办法可以任意选择一种做法而忽略另一种做法？
这是查询：

SELECT DISTINCT 
    n.nhs_number,
    IIF(ch.care_home_date>#2/1/1900#, "TRUE", "FALSE") AS care_home,
    pp.practice

FROM (nhs_no_tbl AS n 
        LEFT JOIN patient_practice_tbl AS pp ON n.nhs_number = pp.nhs_number)
        LEFT JOIN patient_care_home_tbl AS ch ON n.nhs_number = ch.nhs_number;

查询使用的表包含以下数据：

nhs_no_tbl:
|nhs_number|
| -------- |
|1         |
|2         |
|3         |
|4         |

患者练习表：

|nhs_number|practice|
| -------- | ------ |
|1         |GP_A    |
|2         |GP_A    |
|3         |GP_B    |
|4         |GP_A    |
|4         |GP_B    |

患者护理家庭tbl：

|nhs_number|care_home_date|
| -------- | ------------ |
|1         |1/5/2000      |
|1         |1/10/2010     |
|4         |26/10/2017    |

最后，我希望查询返回以下内容：

|nhs_number|Care_home|practice|
| -------- | ------- | ------ |
|1         |TRUE     |GP_A    |
|2         |FALSE    |        |
|3         |FALSE    |        |
|4         |TRUE     |GP_A [or GP_B]  |

标签： sqlms-access

Answer 1

我已经用 CTE 更新了查询，

;WITH cte1 AS ---select all results
(
    SELECT DISTINCT nnt.nhs_number,
    CASE WHEN pcht.care_home_date IS NULL 
         THEN 'FALSE'
         ELSE 'TRUE'
    END AS CareHome,
    ppt.practice,
    rank()OVER(PARTITION BY nnt.nhs_number ORDER BY ppt.practice) AS R 
    FROM #nhs_no_tbl nnt
    LEFT JOIN #patient_practice_tbl ppt ON nnt.nhs_number = ppt.nhs_number
    LEFT JOIN #patient_care_home_tbl pcht ON nnt.nhs_number = pcht.nhs_number
),
CTE2 AS  ---choose who may have multiple pracices
(
    SELECT nhs_number
    FROM CTE1
    WHERE R = 2
),
CTE3 AS --- combine GP_A and GP_B
(
    SELECT t.nhs_number,STRING_AGG(val,',') AS Practices
    FROM 
    (
        SELECT DISTINCT val = cte1.practice, CTE1.nhs_number 
        FROM #nhs_no_tbl nnt 
        INNER JOIN CTE2 ON nnt.nhs_number = CTE2.nhs_number 
        INNER JOIN cte1 ON nnt.nhs_number = CTE1.nhs_number
    ) t
    --RIGHT JOIN #nhs_no_tbl nnt ON t.nhs_number = nnt.nhs_number
    GROUP BY t.nhs_number
)
SELECT cte1.nhs_number,cte1.carehome,cte1.practice, cte3.Practices
FROM CTE1 
LEFT JOIN cte3 ON cte1.nhs_number = CTE3.nhs_number

结果将是

然后接下来您可以将结果存储到临时表中并更新实践不为空的临时表。