mysql - 按不存在的值分组

Question

是否可以按不存在的对象进行分组(抱歉，如果这不是正确的术语)。

例如，如果我有这样一个表:

post_id - 状态

1 - 活跃

2 - 暂停

3 - 按住

状态表的可能值为:active、suspended、hold、deleted

但是在任何给定时间，状态列可能包含也可能不包含所有这些状态类型，但我仍然希望返回一个计数为 0 的状态，即使它不在其中也是如此。

是否可以做(伪查询)这样的事情:

SELECT COUNT(post_id), status(active, suspended, hold, deleted) FROM users GROUP BY 状态

附言在有人建议不要这样做之前，我别无选择，因为这是此 CMS 的属性表结构。

Answer 1

尝试case语句

SELECT SUM(CASE WHEN status = "active" THEN 1 ELSE 0 END) active_count,
       SUM(CASE WHEN status = "suspended" THEN 1 ELSE 0 END) suspended_count,
       SUM(CASE WHEN status = "hold" THEN 1 ELSE 0 END) hold_count,
       SUM(CASE WHEN status = "deleted" THEN 1 ELSE 0 END) deleted_count
FROM posts;

还有这个

SELECT "active", SUM(CASE WHEN status = "active" THEN 1 ELSE 0 END) active_count
FROM posts
UNION
SELECT "suspended", SUM(CASE WHEN status = "suspended" THEN 1 ELSE 0 END) suspended_count
FROM posts
UNION
SELECT "hold", SUM(CASE WHEN status = "hold" THEN 1 ELSE 0 END) hold_count
FROM posts
UNION
SELECT "deleted", SUM(CASE WHEN status = "deleted" THEN 1 ELSE 0 END) deleted_count
FROM posts

还好

SELECT "active", COUNT(*) active_count
FROM posts WHERE status = "active"
UNION
SELECT "suspended", COUNT(*) suspended_count
FROM posts WHERE status = "suspended"
UNION
SELECT "hold", COUNT(*) hold_count
FROM posts WHERE status = "hold"
UNION
SELECT "deleted", COUNT(*) deleted_count
FROM posts WHERE status = "deleted"

参见 SQL fiddle here