一条sql语句实现统计查询
 
如图:程序员在进行如下的统计时,现在提供两种实现方案:
 
 
方案一:运用 SEKECT CASE WHEN 
EXPLAIN  
SELECT  
    count(*) AS '总数',  
    count(  
        CASE oup.status  
        WHEN '1' THEN  
            oup.id  
        END  
    ) AS '未绑定',  
    count(  
        CASE oup.status  
        WHEN '2' THEN  
            oup.id  
        END  
    ) AS '已绑定',  
    count(  
        CASE oup.status  
        WHEN '3' THEN  
            oup.id  
        END  
    )AS  '冻结中'  
FROM  
    lab_org_uc_passport oup  

 

 
显示结果:(按行显示)
 
 
方案二:
SELECT  count(*) AS '总数' FROM lab_org_uc_passport oup   
UNION ALL  
SELECT  count(*) AS '未绑定' FROM lab_org_uc_passport oup WHERE oup.status = '1'  
UNION ALL  
SELECT  count(*) AS '未绑定' FROM lab_org_uc_passport oup WHERE oup.status = '2'  
UNION ALL  
SELECT  count(*) AS '未绑定' FROM lab_org_uc_passport oup WHERE oup.status = '3'  

 

 
显示结果(按列显示)
34  
3  
10  
21