一条sql语句实现统计查询_MySQL

bitsCN.com

一条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  

 

bitsCN.com