mysql-按照两个字段分组，按某个字段统计次数（字段值没有出现计算为0）

建表语句

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `name` varchar(50) DEFAULT NULL,
  `status` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

测试数据
INSERT INTO `test` VALUES ('liuchen', '0');
INSERT INTO `test` VALUES ('liuchen', '0');
INSERT INTO `test` VALUES ('liuchen', '1');
INSERT INTO `test` VALUES ('jiawu', '1');
INSERT INTO `test` VALUES ('jiawu', '1');

 

测试数据

 

 

查询语句

SELECT
t2.NAME,
t1.STATUS,
count(t3.STATUS) num
FROM
(
SELECT
STATUS
FROM
test
GROUP BY
STATUS
) t1
INNER JOIN (
SELECT
NAME
FROM
test
GROUP BY
NAME
) t2 ON 1 = 1
LEFT JOIN test t3 ON t1.STATUS = t3.STATUS
AND t2.NAME = t3.NAME
GROUP BY
t1.STATUS,
t2.NAME
ORDER BY t2.NAME;

 

查询结果

 

参考来源：http://bbs.csdn.net/topics/390638333?page=1

个人博客：www.katts.cn