database - 对于 0 个提取的行 postgresql 返回 0 和 null

Question

我在下面有以下 SQL 查询，它执行时没有错误，并且可以正常运行，唯一没有做的是当查询返回 0 行时，为 INT 返回 0 列，为 VARCHAR 列返回 NULL 列

查询

SELECT
    coalesce((tb_get_customers.customer), 'null') as "CUSTOMER", 
    coalesce((COUNT(*)),0) as "METRICS", 
    coalesce((CONCAT('STATUS: ', severity)), 'null') as "SEVERITY"
FROM 
    tb_get_faults left 
    JOIN tb_get_customers ON tb_get_faults.customer = tb_get_customers.cust_cmdb 
WHERE 
    tb_get_faults.exception = 'NO' 
    AND tb_get_faults.type = 'VM' 
    AND tb_get_faults.customer = 'CLIENT1' 
    AND tb_get_faults.severity= 'HIGH' 
GROUP BY 
    tb_get_faults.customer, 
    tb_get_customers.customer, 
    tb_get_faults.impact 
ORDER BY 
    tb_get_customers.customer ASC;

输出

期望的输出

------------------------------
CUSTOMER | METRICS | SEVERITY
------------------------------
  null   |    0    |   null

Answer 1

使用您的查询创建一个 CTE:

with CTE as (
  <your query here>
)
select * from cte
union all
select 'null', 0, 'null'
where not exists (select 1 from cte)

当 CTE 不返回任何行时，UNION ALL 的第二个查询将返回您期望的行。