mysql中根据一列状态值查询状态的个数

最近写数据库语句，根据状态值查询状态的个数，这个开始难为到我了，不过已经有解决办法了。

在数据库表中有一个字段是状态(zt),这里面有0-5这6个状态，现在查询每个状态的数量，并且显示在一张表中，正常情况下查状态是：

SELECT COUNT(*) dsh FROM `sbbx_gdcl` WHERE `sbbx_gdcl`.`ZT`='0';
SELECT COUNT(*) yxt FROM `sbbx_gdcl` WHERE `sbbx_gdcl`.`ZT`='1';
SELECT COUNT(*) ypg FROM `sbbx_gdcl` WHERE `sbbx_gdcl`.`ZT`='2';
SELECT COUNT(*) yjd FROM `sbbx_gdcl` WHERE `sbbx_gdcl`.`ZT`='3';
SELECT COUNT(*) wxwc FROM `sbbx_gdcl` WHERE `sbbx_gdcl`.`ZT`='4';

SELECT COUNT(*) yswc FROM `sbbx_gdcl` WHERE `sbbx_gdcl`.`ZT`='5';

首先，要先将行变成列：

SELECT t.*,
CASE WHEN t.zt='0' THEN 1 ELSE 0 END AS dsh,
CASE WHEN t.zt='1' THEN 1 ELSE 0 END AS yxt,
CASE WHEN t.zt='2' THEN 1 ELSE 0 END AS ypg,
CASE WHEN t.zt='3' THEN 1 ELSE 0 END AS yjd,
CASE WHEN t.zt='4' THEN 1 ELSE 0 END AS wxwc,
CASE WHEN t.zt='5' THEN 1 ELSE 0 END AS yswc

 FROM `sbbx_gdcl` t 

然后查询每一列的个数：

SELECT IFNULL(SUM(m.dsh),0) DSH,IFNULL(SUM(m.yxt),0) YXT,IFNULL(SUM(m.ypg),0) YPG,IFNULL(SUM(m.yjd),0) YJD,IFNULL(SUM(m.wxwc),0) WXWC,IFNULL(SUM(m.yswc),0) YSWC FROM(

SELECT
CASE WHEN t.zt='0' THEN 1 ELSE 0 END AS dsh,
CASE WHEN t.zt='1' THEN 1 ELSE 0 END AS yxt,
CASE WHEN t.zt='2' THEN 1 ELSE 0 END AS ypg,
CASE WHEN t.zt='3' THEN 1 ELSE 0 END AS yjd,
CASE WHEN t.zt='4' THEN 1 ELSE 0 END AS wxwc,
CASE WHEN t.zt='5' THEN 1 ELSE 0 END AS yswc
 FROM `sbbx_gdcl` t 
) m

这样就可以得到想要的结果了。