因数据量太大，一直显示正在查询，如何优化此sql呢？

Question

SELECT datename(YEAR,isv.GetTime)+'-'+datename(MONTH,isv.GetTime) month,
avg(t1.value),avg(t2.value),avg(t3.value)
from (select isv.value
from InternalSensorDetail isd
inner join InternalSensorValue isv on isv.SensorID=isd.ID
where substring(position,16,1)=1 )t1,
(select isv.value
from InternalSensorDetail isd
join InternalSensorValue isv on isv.SensorID=isd.ID
where substring(position,16,1)=2 )t2,
(select isv.value
from InternalSensorDetail isd
join InternalSensorValue isv on isv.SensorID=isd.ID
where substring(position,16,1)=3 )t3,
InternalSensorValue isv
join sj_InfoMgr_Cabin ca on isv.CainbId=ca.Id

join InternalSensorDetail isd on isv.SensorID=isd.ID
where ca.id=(SELECT CabinId
FROM (
SELECT ROW_NUMBER() OVER (ORDER BY OpenTime desc) AS RNUM , *
FROM sj_InfoMgr_Pouring ) AS sp
WHERE
sp.RN
UM = 3)
group by datename(YEAR,isv.GetTime)+'-'+datename(MONTH,isv.GetTime)

Answer 1

你那个T1、T2、T3三个表基本做相同的事情，为啥不用in合并一下呢，这样就只有一张表了，同时用 1,2,3给value标记一下，因为在avg(value)中你需要通过1/2/3来标识出对应的数据，从而取平均值