一条sql语句实现统计查询_MySQL-mysql教程

一条sql语句实现统计查询

如图：程序员在进行如下的统计时，现在提供两种实现方案：

方案一：运用 SEKECT CASE WHEN　

EXPLAIN  SELECT      count(*) AS &＃39;总数&＃39;,      count(          CASE oup.status          WHEN &＃39;1&＃39; THEN              oup.id          END      ) AS &＃39;未绑定&＃39;,      count(          CASE oup.status          WHEN &＃39;2&＃39; THEN              oup.id          END      ) AS &＃39;已绑定&＃39;,      count(          CASE oup.status          WHEN &＃39;3&＃39; THEN              oup.id          END      )AS  &＃39;冻结中&＃39;  FROM      lab_org_uc_passport oup  

显示结果：(按行显示)

方案二：

SELECT  count(*) AS &＃39;总数&＃39; FROM lab_org_uc_passport oup   UNION ALL  SELECT  count(*) AS &＃39;未绑定&＃39; FROM lab_org_uc_passport oup WHERE oup.status = &＃39;1&＃39;  UNION ALL  SELECT  count(*) AS &＃39;未绑定&＃39; FROM lab_org_uc_passport oup WHERE oup.status = &＃39;2&＃39;  UNION ALL  SELECT  count(*) AS &＃39;未绑定&＃39; FROM lab_org_uc_passport oup WHERE oup.status = &＃39;3&＃39;  

显示结果(按列显示)

34

3

10

21