查询所有区域的数据，没有的，区名要显示，值为0

渡生远方

于 2019-06-03 15:41:18 发布

阅读量77

点赞数

分类专栏： mysql经验

本文链接：https://blog.csdn.net/qq_40798764/article/details/90753180

版权

mysql经验专栏收录该内容

12 篇文章 0 订阅

订阅专栏

查询所有区域的数据，没有的，区名要显示，值为0

SELECT 
t.d_name as areaName,
IF(a.lengths IS NULL, 0, a.lengths) as lengths
 FROM
	(SELECT d_name FROM district)t 
LEFT JOIN
	(SELECT 
	  c.area_name AS areaName,
		COUNT(DISTINCT b.sensor_number) AS lengths 
FROM interface_list b
	        LEFT JOIN building c ON b.b_id=c.b_id
	        LEFT JOIN user_project d ON b.project_list_id=d.project_list_id
	  WHERE 1=1
	      AND d.user_id=27
	  GROUP BY c.area_name)a ON t.d_name=a.areaName ORDER BY t.d_name;

在这里插入图片描述