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

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

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

SELECT COUNT(*) draft  FROM `it_project_info` WHERE `it_project_info`.item_status`=‘1‘;

SELECT COUNT(*) checks FROM `it_project_info` WHERE `it_project_info`.item_status`=‘2‘;

SELECT COUNT(*) plan FROM `it_project_info` WHERE `it_project_info`.item_status`=‘3‘;

SELECT COUNT(*) implement  FROM `it_project_info` WHERE `it_project_info`.item_status`=‘4‘;

SELECT COUNT(*) online  FROM `it_project_info` WHERE `it_project_info`.item_status`=‘5‘;

SELECT COUNT(*) cancel  FROM `it_project_info` WHERE `it_project_info`.item_status`=‘6‘;

SELECT COUNT(*) knot  FROM `it_project_info` WHERE `it_project_info`.item_status`=‘7‘;

首先，要先将行变成列：

SELECT t.*,

CASE WHEN t.`item_status`=1 THEN 1 ELSE 0 END AS draft,

CASE WHEN t.`item_status`=2 THEN 1 ELSE 0 END AS checks,

CASE WHEN t.`item_status`=3 THEN 1 ELSE 0 END AS plan,

CASE WHEN t.`item_status`=4 THEN 1 ELSE 0 END AS implement,

CASE WHEN t.`item_status`=5 THEN 1 ELSE 0 END AS online,

CASE WHEN t.`item_status`=6 THEN 1 ELSE 0 END AS cancel,

CASE WHEN t.`item_status`=7 THEN 1 ELSE 0 END AS knot

FROM `it_project_info` t

然后查询每一列的个数：

SELECT

IFNULL(SUM(m.draft), 0) draftTotal,

IFNULL(SUM(m.checks), 0) checkTotal,

IFNULL(SUM(m.plan), 0) planTotal,

IFNULL(SUM(m.implement), 0) implementTotal,

IFNULL(SUM(m.online), 0) onlineTotal,

IFNULL(SUM(m.cancel), 0) cancelTotal,

IFNULL(SUM(m.knot), 0) knotTotal

FROM

(

SELECT

CASE WHEN t.`item_status`=1 THEN 1 ELSE 0 END AS draft,

CASE WHEN t.`item_status`=2 THEN 1 ELSE 0 END AS checks,

CASE WHEN t.`item_status`=3 THEN 1 ELSE 0 END AS plan,

CASE WHEN t.`item_status`=4 THEN 1 ELSE 0 END AS implement,

CASE WHEN t.`item_status`=5 THEN 1 ELSE 0 END AS online,

CASE WHEN t.`item_status`=6 THEN 1 ELSE 0 END AS cancel,

CASE WHEN t.`item_status`=7 THEN 1 ELSE 0 END AS knot

FROM `it_project_info` t

) m

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