postgresql - 如何在 postgresql 中组合多个 select 语句？

Question

我刚开始学习如何编码。我想知道如何在一个查询中组合多个 SELECT 语句。

SELECT count(*) AS "SENT" FROM message_items WHERE status = 'SENT';

SELECT count(*)  AS "NOT SENT" FROM message_items WHERE status = 'NOT SENT';

SELECT count(*) AS "INVALID NUMBER" FROM message_items WHERE status = 'INVALID NUMBER';

Answer 1

使用条件聚合:

SELECT
    COUNT(*) FILTER (WHERE status = 'SENT') AS "SENT",
    COUNT(*) FILTER (WHERE status = 'NOT SET') AS "NOT SENT",
    COUNT(*) FILTER (WHERE status = 'INVALID NUMBER') AS "INVALID NUMBER"
FROM message_items;

如果你想生成整个表的计数，那么上面的方法是合适的。相反，如果您想为每个其他列生成计数，则只需将该列添加到上面的 select 子句中，然后使用 GROUP BY 按该列进行聚合。