Mysql查询为空赋值为0

最新推荐文章于 2025-06-06 10:28:02 发布

原创最新推荐文章于 2025-06-06 10:28:02 发布 · 4.2w 阅读

12 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一种SQL查询技巧，用于从数据库中提取学校基本信息，并通过左连接和聚合函数计算各学校的校区数量。使用IFNULL函数优雅地处理了校区数可能为空的情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

        SELECT
			a.id AS schoolId,
			a. NAME AS schoolName,
			a.area_id as areaId,
			a.school_type AS schoolType,
			a.school_contact AS schoolContact,
			a.contact_telphone AS contactTelphone,
			a.student_number AS studentNumber,
			DATE_FORMAT(a.account_expire_time,'%Y-%m-%d') AS accountExpireTime,
			DATE_FORMAT(a.create_time, '%Y-%m-%d') AS createTime,
			IFNULL(c.campusNum ,0) as campusNum
		FROM
			school a left join
			(
				SELECT
					count(1) as campusNum,
					b.school_id s_id
				FROM
					campus b
				WHERE
					b.is_deleted = 0
				GROUP BY
					b.school_id
			) c
		on
			a.id = c.s_id
		AND a.organization_id = 1
		AND a.is_deleted = 0

使用IFNULL函数可以完美解决。