sql语句，错行找数据的问题，性能怎么提高

[zhuxiaojun2002]

select system_id,system_name,Status_Time1,(Select max(Status_time1) From System_History Where system_id = sh.System_ID And Status_time1 < sh.Status_Time1)
from System_History sh
where System_Parent_ID=0 and dateadd(day,-1,getdate())<Status_Time1 and sh.System_Status1>0
order by Status_Time1,System_ID

我要得一个列表，包含这个system_id上一次状态时间。1w条数据，查询要4秒了，怎么样提高这个子查询的性能啊。

[Tiger_Zhao]

一定要在 (system_id,Status_Time1,System_Status1) 上建索引

        SELECT *
          FROM (
                select system_id,system_name,Status_Time1
                  from System_History sh
                 where System_Parent_ID=0 
                   and dateadd(day,-1,getdate())<Status_Time1
                   and sh.System_Status1>0
               ) t1
   OUTER APPLY (
                    Select TOP 1 Status_time1 AS Status_time0
                      From System_History
                     Where system_id = sh.System_ID
                       And Status_time1 < sh.Status_Time1
                  ORDER BY Status_time1 DESC 
               ) t2
order by Status_Time1,System_ID

[xxfvba]

--没做过测试 with t as (select System_Parent_ID,System_Status1,system_id,system_name,Status_Time1,rn=row_number() over (partition by system_id order by Status_Time1) from System_History) select t1.system_id,t1.system_name,t1.Status_Time1,t2.Status_Time1 from t t1 left join t t2 on t1.system_id=t2.system_id and t1.rn=t2.rn+1 where t1.System_Parent_ID=0 and dateadd(day,-1,getdate())<t1.Status_Time1 and t1.System_Status1>0 order by t1.Status_Time1,t1.System_ID

[还在加载中灬]

--方法1 速度未知
;WITH CTE AS(
	SELECT system_id,system_name,Status_Time1
		,ROW_NUMBER()OVER(PARTITION BY system_id ORDER BY Status_Time1)RN
	FROM System_History
	WHERE System_Parent_ID=0 AND System_Status1>0
)
SELECT T1.system_id,T1.system_name,T1.Status_Time1,T2.Status_Time1
FROM CTE T1
	LEFT JOIN CTE T2 ON T1.system_id=T2.system_id AND T1.RN=T2.RN+1
WHERE T1.Status_Time1>dateadd(day,-1,getdate())
--方法2 应该和子查询差不多
SELECT T1.system_id,T1.system_name,T1.Status_Time1,T3.Status_Time1
FROM System_History T1
	OUTER APPLY(SELECT TOP 1 Status_Time1 FROM System_History T2
				WHERE T1.system_id=T2.system_id AND T1.Status_Time1>T2.Status_Time1
				ORDER BY T2.Status_Time1 DESC)T3
where T1.System_Parent_ID=0 and dateadd(day,-1,getdate())<T1.Status_Time1 and T1.System_Status1>0
--综合1和2的方法
;WITH CTE AS(
	SELECT system_id,system_name,Status_Time1
		,ROW_NUMBER()OVER(PARTITION BY system_id ORDER BY Status_Time1)RN
	FROM System_History
	WHERE System_Parent_ID=0 AND Status_Time1>dateadd(day,-1,getdate()) AND System_Status1>0
)
SELECT T1.system_id,T1.system_name,T1.Status_Time1,ISNULL(T2.Status_Time1,T4.Status_Time1)
FROM CTE T1
	LEFT JOIN CTE T2 ON T1.system_id=T2.system_id AND T1.RN=T2.RN+1
	OUTER APPLY(SELECT TOP 1 Status_Time1 FROM System_History T3
				WHERE T2.system_id IS NULL AND T1.system_id=T3.system_id AND T1.Status_Time1>T3.Status_Time1
				ORDER BY T3.Status_Time1 DESC)T4

[zdrone]

1 使用witch CTE 语句 2 where 条件 和 主键 加索引 其他的应该也没啥好多做的了，无非就是主键到底是用uuid,自增列,int值得问题。

[tcmakebest]

看 where 用到哪些字段, 全部建索引