对于多表联合查询Count函数返回NULL

SELECT
	id,
	msg_title,
	msg_type,
	content,
	start_tasktime,
	end_tasktime,
	create_psn,
	create_time,
	update_psn,
	update_time,
	STATUS,
	readNum,
	time_num,
	CASE release_status
WHEN 0 THEN
	'未发布'
WHEN 1 THEN
	'已发布'
END release_status
FROM
	sys_message msg
LEFT JOIN (
	SELECT
		COUNT(pk_id) AS readNum,
		pk_id
	FROM
		sys_message_touser
	WHERE
		read_status = '1'
	GROUP BY
		pk_id
) AS num ON num.pk_id = msg.id

当我的sys_message_touser表中符合条件的数据为0个时,一直返回NULL,改成这样就好了

SELECT
	id,
	msg_title,
	msg_type,
	content,
	start_tasktime,
	end_tasktime,
	create_psn,
	create_time,
	update_psn,
	update_time,
	STATUS,
	COALESCE (readNum, 0) readNum,
	time_num,
	CASE release_status
WHEN 0 THEN
	'未发布'
WHEN 1 THEN
	'已发布'
END release_status
FROM
	sys_message msg
LEFT JOIN (
	SELECT
		COUNT(pk_id) as readNum,
		pk_id
	FROM
		sys_message_touser
	WHERE
		read_status = '1'
	group by
		pk_id
) as num ON num.pk_id = msg.id

COALESCE函数可以传入多个参数,如果所有参数都为NULL则返回NULL,否则返回第一个不为NULL的值