在一个查询中从两个表中统计MySQL记录？

Question

我有三个MySQL表：

patient:
    paID, paCode, paAccountID (foreign key)

test
    tsID, tsName, tsPatientID (foreign key), tsAccountID (foreign key)

account
    acID etc.

现在我想要统计与特定的 acID=1 相关联的 paID 和 tsID 的数量。有 6 个 tsID 和 4 个 paID 与 acID=1 相关联。

SELECT Count(paID) AS paCount FROM patient WHERE paAccountID=1
SELECT Count(tsID) AS tsCount FROM test WHERE tsAccountID=1

尝试将两者合并成一个查询...

SELECT Count(tsID) AS tsCount, Count(paID) AS paCount
FROM test LEFT JOIN patient ON tsPatientID = paID 
WHERE tsAccountID=1 

这样行不通，两个计数器都返回6。如何才能做到正确呢？

- michi

Answer 1

假设你的tsId和paId是唯一键，那么我认为这个应该可以工作：

SELECT Count(DISTINCT t.tsID) AS tsCount, 
    Count(DISTINCT p.paID) AS paCount
FROM account a 
    LEFT JOIN test t ON a.acId = t.tsAccountId
    LEFT JOIN patient p ON a.acId = p.paAccountId
WHERE a.acId = 1

这里是SQL Fiddle。

请注意：如果不在账户表上进行联接（并将其用作主表），那么如果测试表或患者表对于特定的账户ID没有数据，则查询将对每个表都返回0个结果--这可能是不正确的。

- sgeddes

---

@michi -- 没问题，很高兴我们能帮助到您！ - sgeddes

Answer 2

SELECT
    COUNT(DISTINCT tsID) AS tsCount,
    COUNT(DISTINCT paID) AS paCount
FROM
    test
    CROSS JOIN patient
WHERE
    tsAccountID = 1
    AND paAccountID = 1

- Explosion Pills

---

两个计数器都返回24，猜测是缺少了distinct。 - michi

@ExplosionPills--我不会将test作为您的主表加入--如果test中没有结果会怎样：http://sqlfiddle.com/#!2/417e1/1--我使用了account作为我的主要表。除此之外，看起来很不错！ - sgeddes

CROSS JOIN 打字更少，也更方便;-) - michi

@michi 注意，你不需要实际键入“CROSS”，它只是表示这是一个交叉连接。你可以只键入“JOIN”，它会有同样的效果。 - Explosion Pills

@michi -- 注意使用CROSS JOIN时要小心 -- 如果任何一个表没有数据，它们将无法工作 -- 请参见上面的Fiddle。这就是为什么LEFT JOIN可能是更好的方法...祝你好运！ - sgeddes

Answer 3

你的join方法返回所有相关的行，因此你的count将始终返回返回的所有行的数量。

在这种情况下，我会在主查询中使用(select count(*) from [Table] Where [id=x]) as [tsCount]作为一个字段。

- a7mad.3ezz