MySQL 为空返回0

数据库在查询的时候 如果要查询的字段没有数据

SELECT
	CASE
			b.face_dev_area 
			WHEN 1 THEN
			'办公楼' 
			WHEN 2 THEN
			'北疆库区' 
			WHEN 3 THEN
			'南疆库区' 
			WHEN 4 THEN
			'油码头' 
		END face_dev_area_name,
	COUNT( DISTINCT a.user_id ) AS count_num 
FROM
	fvcs_face_reco_record a,
	fvcs_face_reco_device b 
WHERE
	DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= date( a.reco_time ) 
	AND a.face_dev_id = b.face_dev_id 
GROUP BY
	face_dev_area_name 

就像这个我想要4个区域都查一遍 但是区域4没有数据 查询返回的数据就没有这一行 如何让他返回数据呢

我这里用了两种方法

第一种 我觉得方便的

[参考](mysql查询最近7天的数据，没有数据自动补0_Kevin.yang专栏-CSDN博客_mysql 查询7天数据)

1.构建一个最近七天的结果集，然后和查询的结果集合做left join

SELECT
	a.face_dev_area_name,
	b.count_num AS count_num 
FROM
	( SELECT '办公楼' AS face_dev_area_name UNION ALL SELECT '北疆库区' AS face_dev_area_name UNION ALL SELECT '南疆库区' AS face_dev_area_name UNION ALL SELECT '油码头' AS face_dev_area_name ) a
	LEFT JOIN (
	SELECT
	CASE
			b.face_dev_area 
			WHEN 1 THEN
			'办公楼' 
			WHEN 2 THEN
			'北疆库区' 
			WHEN 3 THEN
			'南疆库区' 
			WHEN 4 THEN
			'油码头' 
		END face_dev_area_name,
	COUNT( DISTINCT a.user_id ) AS count_num 
FROM
	fvcs_face_reco_record a,
	fvcs_face_reco_device b 
WHERE
	DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= date( a.reco_time ) 
	AND a.face_dev_id = b.face_dev_id 
GROUP BY
	face_dev_area_name 
	) b ON a.face_dev_area_name = b.face_dev_area_name;

2.需要把NULL设置为0，利用ifnull函数即可

SELECT
	a.face_dev_area_name,
	ifnull( b.count_num, 0 ) AS count_num 
FROM
	( SELECT '办公楼' AS face_dev_area_name UNION ALL SELECT '北疆库区' AS face_dev_area_name UNION ALL SELECT '南疆库区' AS face_dev_area_name UNION ALL SELECT '油码头' AS face_dev_area_name ) a
	LEFT JOIN (
	SELECT
	CASE
			b.face_dev_area 
			WHEN 1 THEN
			'办公楼' 
			WHEN 2 THEN
			'北疆库区' 
			WHEN 3 THEN
			'南疆库区' 
			WHEN 4 THEN
			'油码头' 
		END face_dev_area_name,
	COUNT( DISTINCT a.user_id ) AS count_num 
FROM
	fvcs_face_reco_record a,
	fvcs_face_reco_device b 
WHERE
	DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= date( a.reco_time ) 
	AND a.face_dev_id = b.face_dev_id 
GROUP BY
	face_dev_area_name 
	) b ON a.face_dev_area_name = b.face_dev_area_name;

第二种 循环查询

[参考]mysql循环执行sql语句，与大部分博客不一样的正确写法_L9009121314的博客-CSDN博客

DROP PROCEDURE
IF
	EXISTS proc_initData;# 删除存储过程

DELIMITER //# 设置分割符1，注意：与上一步 删除存储过程 顺序不能调换
CREATE PROCEDURE count_week () BEGIN
	DECLARE
		i INT DEFAULT 1;
	WHILE
			i <= 4 DO
		SELECT
		CASE
				b.face_dev_area 
				WHEN 1 THEN
				'办公楼' 
				WHEN 2 THEN
				'北疆库区' 
				WHEN 3 THEN
				'南疆库区' 
				WHEN 4 THEN
				'油码头' 
			END face_dev_area_name,
	COUNT( DISTINCT a.user_id ) AS count_num 
FROM
	fvcs_face_reco_record a,
	fvcs_face_reco_device b 
WHERE
	DATE_SUB( CURDATE(), INTERVAL 7 DAY ) <= date( a.reco_time ) 
	AND a.face_dev_id = b.face_dev_id 
	AND b.face_dev_area = 1 
	SET i = i + 1;

END WHILE;

END // 
DELIMITER;# 设置分割符2
CALL count_week ();# 调用存储过程，注意：与上一步 设置分割符2 顺序不能调

这种方法就是代码思路的更改变量 循环 不过这样会返回多个查询结果

有时还会出现存储过程不清空的问题 不推荐使用