花公子丶
2016-01-06 11:42求帮忙优化我这条sql语句
首先说一下下面sql语句要用到的表的基本情况:
一、有三张表,一张叫equipment,一张叫sensor,一张叫sensorInputData
二、三表关系是equipment下有多个sensor,sensor下有多个sensorInputdata,所以sensor下有一个equipment的主键quipmentId做外键,sensorInputData下也有一个sensor的主键sensorId做外键,其他都是各自表的属性,应该能比较清晰的看出来
下面是我的查询代码:
select
equipment.equipmentName as 设备,
equipment.longitude as 经度,
equipment.latitude as 纬度,
equipment.equipmentType as 设备类型,
data.数据类型 as 数据类型,
Convert(decimal(18,2),data.数据) as 数据,
data.单位 as 单位,
convert(varchar(20), data.日期,120) as 日期
from EquipmentMessage as equipment
left join
(
select
sensor.sensorUnit as 单位,
sensorData.value as 数据,
sensorData.date as 日期,
sensorData.valueType as 数据类型,
sensor.equipmentId as equipmentId
from Sensor as sensor
join SensorInputData as sensorData on sensor.sensorId=sensorData.sensorId
and not exists
(
select 1 from SensorInputData as t where t.sensorId=sensorData.sensorId and t.date>sensorData.date
)
) as data on equipment.equipmentId=data.equipmentId
左连接是查出传感器(sensor表)与传感器的第一条数据(sensorInputData表)
- 点赞
- 回答
- 收藏
- 复制链接分享
1条回答
为你推荐
- 有大佬会优化sql语句吗?
- mysql
- 2个回答
- 帮忙SQLSERVER通过存储写ORACLE语句优化
- it技术
- 互联网问答
- IT行业问题
- 计算机技术
- 编程语言问答
- 0个回答
- 做了一个小时的面试题(没有过 希望大家帮忙答下 虽然很幼稚 毕竟每个人都是这么过来的吗 感激了!)
- it技术
- 互联网问答
- IT行业问题
- 计算机技术
- 编程语言问答
- 2个回答
- PreparedStatement 预编译原理
- 企业应用
- 0个回答
- 数据库查询效率问题优化
- 数据库
- 0个回答
换一换