最近写数据库语句,根据状态值查询状态的个数,这个开始难为到我了,不过已经有解决办法了。
在数据库表中有一个字段是状态(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(
SELECTCASE 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
这样就可以得到想要的结果了。