mysql-按照两个字段分组,按某个字段统计次数(字段值没有出现计算为0)

时间:2021-04-20 15:06:06

建表语句

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');


测试数据

mysql-按照两个字段分组,按某个字段统计次数(字段值没有出现计算为0)mysql-按照两个字段分组,按某个字段统计次数(字段值没有出现计算为0)


查询语句

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;


查询结果

mysql-按照两个字段分组,按某个字段统计次数(字段值没有出现计算为0)mysql-按照两个字段分组,按某个字段统计次数(字段值没有出现计算为0)

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