php - Mysql选择多个计数给出错误的值

Question

我正在尝试查找患者的约会和消息计数。我的表格记录如下 3 表格患者、约会和消息
病人表

pid     fname   lname
1      john     sid
2      rother   ford
3      megan    rough
4      louis    kane

约会表

id  pid     appointment_date
1   1   2015-08-04
2   2   2015-08-05
3   1   2015-08-06
4   1   2015-08-07
5   3   2015-08-07
6   2   2015-08-08
7   4   2015-08-13
8   1   2015-08-12

消息表

id  pid     description     message_date
1   2        join           2015-08-04
2   2        update         2015-08-05
3   3        join           2015-08-05
4   4        update         2015-08-10
5   3        test           2015-08-07

因此，如果编写查询来查找计数，我会得到错误的值

SELECT pd.fname,pd.lname , pd.pid, COUNT( a.id ) AS app_cnt, COUNT(   m.id   ) AS mes_cnt
FROM patient pd
LEFT OUTER JOIN appointments a   ON a.pid = pd.pid
LEFT OUTER JOIN messages m ON m.pid = pd.pid
GROUP BY pd.pid
ORDER BY pd.pid  

fname   lname   pid     app_cnt     mes_cnt
john    sid     1         4            0
rother  ford    2         4            4
megan   rough   3         2            2
louis   kane    4         1            1

这里 pid 1 有 4 个约会和 0 条消息，pid 2 有 2 个约会和 2 条消息但是得到了错误的值。

有人可以帮忙解决这个问题吗？我对为此编写子查询不感兴趣。

功能看起来很简单，但我真的面临编写查询的问题。

还有什么建议吗

Answer 1

在彻底分析你的问题和表之后，它不能直接使用简单的查询来完成，因为 LEFT OUTER JOIN 正在返回一些多余的记录，这就是为什么要过滤它，你将不得不使用临时表和将查询修改为:

Select temp.fname, temp.lname, temp.pid, a_count, count(m.pid) as m_count from
(SELECT fname,lname,pd.pid, count(a.pid) as a_count
FROM patients pd
LEFT OUTER JOIN appointments a   ON a.pid = pd.pid group by pd.pid) temp
LEFT OUTER JOIN messages m ON m.pid = temp.pid
group by temp.pid

解释:

它将加入 patients 和 appointments 表，并按 pid 对它们进行分组，以便 messages 来自 消息表不要为每个patients.pid重复。

错误的结果是左外连接的结果，因为它为此查询提供了错误的结果

SELECT *
FROM patients pd
LEFT OUTER JOIN appointments a   ON a.pid = pd.pid
LEFT OUTER JOIN messages m ON m.pid = pd.pid

由于我们需要限制前两次连接的结果，因此临时表是必要的。