yu766588220
花公子丶
2016-01-06 11:42

求帮忙优化我这条sql语句

  • 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条回答

为你推荐

换一换