mysql - 组合字段时获取计数

Question

编辑:我更改了表格。请参阅更新后的表格:

在我的示例表中

post            status      
-------------   -------------
post1           delivered
post2           undelivered
post3           delayed
post4           delivered
post5           undelivered

是否可以在单个查询中组合获取已送达数量和未送达+延迟数量？

像这样:

post            status      
-------------   -------------
delivered       2
undelivered     3

Answer 1

SELECT
    SUM(IF(status = 'delivered', 1, 0)) as `delivered_count`,
    COUNT(postid) as `all_count`
FROM
    table

应该可以正常工作。此帐户只有已交付 和未交付 状态 - 如果有更多状态，但您仍然只想计算这两个状态，您可以执行以下操作:

SELECT
    SUM(IF(status = 'delivered', 1, 0)) as `delivered_count`,
    SUM(IF(status = 'delivered' OR status = 'undelivered', 1, 0)) as `all_count`
FROM
    table

如果您想要单独计数已送达和未送达，您可以使用GROUP BY:

SELECT
    COUNT(postid),
    status
FROM
    table
WHERE
    status IN ('delivered', 'undelivered')
GROUP BY
    status