mysql分组查询显示为0的数据，计算百分比

1.首先根据左连接方式将分组为0的数据也显示出来

使用LEFT JOIN将子查询与主表所有的类型连接起来，确保即使统计结果为0的数据也会显示出来。最后使用IFNULL函数来将NULL值替换为0。

SELECT
	b.business_sub_type,
	ifnull( c.alarmNum, 0 ) AS alarmNum
FROM
	( SELECT '工艺类' AS business_sub_type UNION SELECT '危险源类' UNION SELECT '危化品类' ) AS b
        
LEFT JOIN (
	SELECT
		a.business_sub_type,
		count( 1 ) AS alarmNum 
	FROM
		alarm_re a 
	WHERE
		a.del_flag = 0 
		AND a.business_sub_type IN ( '工艺类', '危险源类', '危化品类' ) 
		AND DATE_FORMAT( a.alarm_time, '%Y-%m-%d' ) = CURDATE() 
	GROUP BY
		a.business_sub_type 
	) c ON b.business_sub_type = c.business_sub_type

2.计算百分比

使用CROSS JOIN来计算总的数量。然后在主查询中，将每个业务类型的数量与总数相除，并使用FORMAT函数将结果格式化为百分比形式。最后，使用CONCAT函数将百分比值与百分号连接在一起。

SELECT
	b.business_sub_type,
	ifnull( c.alarmNum, 0 ) AS alarmNum,
	CONCAT( FORMAT(( ifnull( c.alarmNum, 0 ) / total.total_count ) * 100, 2 ), '%' ) AS percentage 
FROM
	( SELECT '工艺类' AS business_sub_type UNION SELECT '危险源类' UNION SELECT '危化品类' ) AS b
LEFT JOIN (
	SELECT
		a.business_sub_type,
		count( 1 ) AS alarmNum 
	FROM
		alarm_re a 
	WHERE
		a.del_flag = 0 
		AND a.business_sub_type IN ( '工艺类', '危险源类', '危化品类' ) 
		AND DATE_FORMAT( a.alarm_time, '%Y-%m-%d' ) = CURDATE() 
	GROUP BY
		a.business_sub_type 
	) c ON b.business_sub_type = c.business_sub_type
        
CROSS JOIN (
	SELECT
		count( 1 ) AS total_count 
	FROM
		alarm_re
	WHERE
		del_flag = 0 
	AND business_sub_type IN ( '工艺类', '危险源类', '危化品类' ) 
	AND DATE_FORMAT( alarm_time, '%Y-%m-%d' ) = CURDATE()) total