MySQL：如何分别对列进行计数？

Question

通常我对 SQL 查询非常有信心，但是这个让我摸不着头脑。我觉得这应该是一个快速解决方案，但我只是没有看到它。

我正在尝试在一个查询中对同一个表上的多个值进行计数。

不要介意“0000000000000000”，它只是代表一个空字节数组。

有没有一种简单的方法来组合这些查询？

SELECT COUNT(ssn)
FROM patients
WHERE ssn="0000000000000000";

SELECT COUNT(firstname)
FROM patients
WHERE firstname="0000000000000000"

SELECT COUNT(lastname)
FROM patients
WHERE lastname="0000000000000000"

etc...

Answer 1

SELECT SUM(CASE WHEN ssn = '0000000000000000' THEN 1 ELSE 0 END) AS ssn_count,
       SUM(CASE WHEN firstname = '0000000000000000' THEN 1 ELSE 0 END) AS first_count,
       SUM(CASE WHEN lastname = '0000000000000000' THEN 1 ELSE 0 END) AS last_count
    FROM patients
    WHERE ssn = '0000000000000000'
        OR firstname = '0000000000000000'
        OR lastname = '0000000000000000'

Answer 2

你可以做这样的事情 -

SELECT COUNT(ssn) AS patient_count, 'ssn' AS count_type
FROM patients
WHERE ssn="0000000000000000";
UNION
SELECT COUNT(firstname) AS patient_count, 'firstname' AS count_type
FROM patients
WHERE firstname="0000000000000000"
UNION
SELECT COUNT(lastname) AS patient_count, 'lastname' AS count_type
FROM patients
WHERE lastname="0000000000000000"

Answer 3

尝试使用 UNION

SELECT COUNT(ssn)
FROM patients
WHERE ssn="0000000000000000";
UNION
SELECT COUNT(firstname)
FROM patients
WHERE firstname="0000000000000000"
UNION    
SELECT COUNT(lastname)
FROM patients
WHERE lastname="0000000000000000"

Answer 4

我想这会起作用吗？

SELECT *
FROM
(SELECT COUNT(ssn) AS ssn_count
 FROM patients
 WHERE ssn="0000000000000000") AS ssn
CROSS JOIN
(SELECT COUNT(firstname) AS firstname_count
 FROM patients
 WHERE firstname="0000000000000000") AS firstname
CROSS JOIN
(SELECT COUNT(lastname) AS lastname_count
 FROM patients
 WHERE lastname="0000000000000000") AS lastname