mysql - 多表条件下的行数

Question

我想根据每个条件计算表中的行。我以某种方式做到了这一点，但它不起作用。

$sql = "SELECT
 (SELECT COUNT(name) FROM orders) as total,
  UNION ALL
 (SELECT COUNT(name) FROM orders  WHERE status='1') as ok,
  UNION ALL
 (SELECT COUNT(name) FROM orders  WHERE status='2') as ko";

Answer 1

使用条件聚合:

select count(*) as total, sum(status = 1) as ok, sum(status = 2) as ko
from orders;

注意:这假设status确实存储为数字。不要对数字常量使用单引号(当您真正想要一个字符串时，请使用它们)。