多表关联查询,工作中用到的sql

最新推荐文章于 2025-03-28 10:33:34 发布

原创最新推荐文章于 2025-03-28 10:33:34 发布 · 152 阅读

0 ·

CC 4.0 BY-SA版权

Java 专栏收录该内容

69 篇文章

订阅专栏

本文深入解析了一段复杂的SQL查询语句，详细介绍了如何通过左连接和子查询来获取特定的数据集合，包括数据ID、区域编码、地址编码、插入时间及最大数据值等关键字段，并展示了如何在两个数据表中进行数据匹配和筛选。

select s1.data_id, s1.DISTRICTBCDID, s1.ADDRESSID, s1.INSERT_TIME, max(s1.DATA_VALUE) as DATA_VALUE, max(s2.EPU_LOCAL) as EPU_LOCAL

 from 
S_SENSOR_DATA s1 left join (

select s.data_id, s.DISTRICTBCDID, s.ADDRESSID, mb.EPU_LOCAL, max(s.INSERT_TIME) as INSERT_TIME
from S_SENSOR_DATA s 
left join T_METERBOX mb on s.DISTRICTBCDID = mb.C_DISTRICTBCDID and s.ADDRESSID = mb.C_ADDRESSID
where (s.data_id = 5 or s.data_id=8) -- and mb.EPU_LOCAL like '2栋%'
group by s.data_id, s.DISTRICTBCDID, s.ADDRESSID, mb.EPU_LOCAL) 
s2 on s1.data_id = s2.data_id and s1.DISTRICTBCDID = s2.DISTRICTBCDID and s1.ADDRESSID = s2.ADDRESSID and s1.INSERT_TIME = s2.INSERT_TIME
where s2.data_id is not null
group by s1.data_id, s1.DISTRICTBCDID, s1.ADDRESSID, s1.INSERT_TIME