mysql - 检索多个计数的 SQL 语句

Question

table account:
    acID (pri, uni, a-i)
    ...

table patient:
    paID (pri, uni, a-i)
    paAccountID (int, foreign key)
    ...

table test:
    tsID (pri, uni, a-i)
    tsAccountID (int, foreign key)
    tsPatientID (int, foreign key)
    tsStatus (int, ranges 0 to 3)
    tsFlag (int, ranges 0 to 1)

从 patient 中检索数据和每个患者的 tsID 数量:

SELECT patient.*, Count(tsID) as tsCount   
FROM account 
LEFT JOIN patient ON acID = paAccountID
LEFT JOIN test ON paID = tsPatientID
WHERE paAccountID=1
GROUP BY paID

现在，我想检索 tsFlag=1 的数量、tsStatus=0 的数量、'tsStatus=1' 的数量 ... 的数量>tsStatus=3.

感谢爆丸的回答:

SELECT patient.*, Count(tsID), SUM(tsStatus=0), 
SUM(tsStatus=1), SUM(tsStatus=2), SUM(tsStatus=3), SUM(tsFlag=1)
FROM account 
LEFT JOIN patient ON acID = paAccountID
LEFT JOIN test ON paID = tsPatientID
WHERE paAccountID=1
GROUP BY paID

工作起来很有魅力，但如果 Count=0，则 SUM 为 NULL- 我怎样才能得到 0 呢？

Answer 1

您可以使用 SUM 获得 X = Y 的计数:

SELECT patient.*, SUM(tsStatus = 1) AS tsStatus1
FROM ...

您也可以对其他列执行此操作。

您可以使用 IFNULL 或 COALESCE 指定默认的非空值:

IFNULL(SUM(tsStatus = 3), 0);