如何解决联合查询中记录数量差异问题

Question

我有一个技术员和他们的病人数量的列表，当我点击一个技术员时，我会得到病人的名单和他们的详细信息。为此，我有一个查询返回每个技术人员的患者数量，另一个查询返回患者的记录。

SELECT *, SUM(Rcount) as Number_of_patients
FROM
(           
    SELECT users.users_id, users.name, patients.patients_id, count(*) as Rcount 
    FROM pecs 
    INNER JOIN users ON pecs.techniciens_id = users.users_id 
    INNER JOIN titles ON users.titles_id = titles.titles_id 
    INNER JOIN patients ON patients.patients_id = pecs.patients_id 
    GROUP BY users_id 
    UNION ALL
    SELECT users.users_id, users.name, patients.patients_id, count(*) as Rcount
    FROM followup 
    INNER JOIN users ON followup.technician_id = users.users_id 
    INNER JOIN titles ON users.titles_id = titles.titles_id 
    INNER JOIN pecs ON pecs.pecs_id = followup.pecs_id 
    INNER JOIN patients ON patients.patients_id = pecs.patients_id 
    GROUP BY users_id
)x 
GROUP BY users_id ORDER BY last_name ASC

结果是：

users_id    |   name    | Number_of_patients
40          |   ABABAB  | 223

即technician_ID = 40的223名患者

现在，要查看此技术人员的患者列表，我有以下查询：

SELECT *
FROM
(
    SELECT patients.patients_id, patients.name
    FROM pecs 
    LEFT JOIN users ON pecs.techniciens_id = users.users_id  
    LEFT JOIN titles ON users.titles_id = titles.titles_id 
    INNER JOIN patients ON patients.patients_id = pecs.patients_id 
    WHERE pecs.techniciens_id = 40
    #GROUP BY patients_id
    UNION ALL       
    SELECT patients.patients_id, patients.name
    FROM followup 
    LEFT JOIN users ON followup.technician_id = users.users_id 
    LEFT JOIN titles ON users.titles_id = titles.titles_id 
    LEFT JOIN pecs ON pecs.pecs_id = followup.pecs_id 
    INNER JOIN patients ON patients.patients_id = pecs.patients_id 
    WHERE followup.technician_id = 40
    #GROUP BY patients_id
)x
GROUP BY patients_id ORDER BY last_name ASC

现在，我得到了相同数量的记录(223)，但是有重复的患者行，...I需要帮助如何在没有重复的情况下为每个技术人员获得正确的患者数量。

有人能帮帮忙吗？

Answer 1

我不确定我是否完全理解了你的问题。无论如何，我会尽量让事情变得尽可能简单。关于您的"count“查询，您可以从下面这样的内容开始，我删除了一些我认为对计数没有用处的文件。此查询将为您提供避免多个id的计数(我在两个select和UNION中使用DISTINCT来删除两个select之间可能存在的重复项)。也许可以消除一些连接(但我不知道整个结构)。请遵循Tim B.有关格式的建议，并在发布问题时小心。

SELECT USERS_ID, COUNT(*) AS PATIENTS_COUNT
FROM (
        SELECT DISTINCT USERS.USERS_ID, PATIENTS.PATIENTS_ID
        FROM PECS
        INNER JOIN USERS ON PECS.TECHNICIENS_ID = USERS.USERS_ID
        INNER JOIN TITLES ON USERS.TITLES_ID = TITLES.TITLES_ID
        INNER JOIN PATIENTS ON PATIENTS.PATIENTS_ID = PECS.PATIENTS_ID
        UNION 
        SELECT DISTINCT USERS.USERS_ID, PATIENTS.PATIENTS_ID 
        FROM FOLLOWUP
        INNER JOIN USERS ON FOLLOWUP.TECHNICIAN_ID = USERS.USERS_ID
        INNER JOIN TITLES ON USERS.TITLES_ID = TITLES.TITLES_ID
        INNER JOIN PECS ON PECS.PECS_ID = FOLLOWUP.PECS_ID
        INNER JOIN PATIENTS ON PATIENTS.PATIENTS_ID = PECS.PATIENTS_ID
    ) A