mysql 按两个维度（字段）统计

需求：按设备种类 设备状态统计特种设备数量

前端设计图：

表（设备表）设计：一种设备可有多个状态，即统计每种设备处于各个状态的数量

sql语句：先按type、state分组统计各个type各个state的数量，再按类型分组 不同的state作为不同的字段展示统计

SELECT
	CASE k.type
	WHEN 1 THEN
		"电梯"
		WHEN 2 THEN
		"压力容器"
		WHEN 3 THEN
		"锅炉"
		WHEN 4 THEN
		"叉车"
		WHEN 5 THEN
		"行车"
	ELSE
		""
END as type,
	sum( CASE WHEN k.state = 1 THEN k.times ELSE 0 END ) "normal",
	sum( CASE WHEN k.state = 2 THEN k.times ELSE 0 END ) "repair",
	sum( CASE WHEN k.state = 3 THEN k.times ELSE 0 END ) "broken"
FROM
	(
	SELECT
		type,
		state,
		count(1) times 
	FROM
		t_enterprise_special 
		WHERE type is not null
-- 		and enterprise_id="666"
	GROUP BY
		type,
		state 
	) k 
GROUP BY
	k.type
-- 	ORDER BY k.type ASC
	;