问题描述

求帮忙优化我这条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表）

解决方案

t.sensorId=sensorData.sensorId and t.date>sensorData.date
这两个字段要加上索引，否则查询很慢的。