如何在此查询中包括等于0的计数？ [英] How to include count equal to 0 on this query?

问题描述

我的查询

select state,count(*) from crm_lead group by state;

返回

state   count
done    794
open    560
pending 3
draft   8
cancel  1

，但是有时候我的桌子上没有取消任务

but sometime when I have no 'cancel' task in my table

state   count
done    794
open    560
pending 4
draft   8

我想在结果中包括count = 0，什么是最好的查询？

I would like to include count = 0 in the result, what is the best query ?

推荐答案

SELECT  a.STATE , 
        COALESCE(b.count, 0) AS Count
FROM 
    (
        SELECT 'done' AS STATE
        UNION
        SELECT 'open' AS STATE
        UNION
        SELECT 'pending' AS STATE
        UNION
        SELECT 'draft' AS STATE
        UNION
        SELECT 'cancel' AS STATE
    ) a LEFT JOIN 
    (
        SELECT  STATE , 
                count(*) AS count
        FROM    crm_lead
        GROUP BY STATE
    ) b ON a.STATE = b.STATE

这篇关于如何在此查询中包括等于0的计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！