看看这段SQL代码如何优化下 PK
SET @vReturnTime =(SELECT ReturnTime FROM car_arrive_info where ListId = @iListId AND (ReturnTime IS NOT NULL AND ReturnTime<>''))
SET @iiSn =(SELECT SN FROM CAR_ARRIVE_INFO WHERE ListId=@iListId AND ReturnTime=@vReturnTime)
IF EXISTS (SELECT ReturnTime FROM CAR_ARRIVE_INFO WHERE @vArriveTime < @vReturnTime AND ListId = @iListId AND SN <@iiSn )
BEGIN
UPDATE CAR_ARRIVE_INFO
SET ArriveTime = @vArriveTime,Distnce=@iDistnce,State=1,OutTime = @vArriveTime
WHERE ListId = @iListId AND SN = @iSn AND State=0 AND SN <@iiSn AND @vArriveTime < @vReturnTime
END
IF EXISTS (SELECT ReturnTime FROM CAR_ARRIVE_INFO WHERE @vArriveTime > @vReturnTime AND ListId = @iListId AND SN >= @iiSn)
BEGIN
UPDATE CAR_ARRIVE_INFO
SET ArriveTime = @vArriveTime,Distnce=@iDistnce,State=1,OutTime = @vArriveTime
WHERE ListId = @iListId AND SN = @iSn AND State=0 AND SN >=@iiSn AND @vArriveTime >= @vReturnTime
END
[解决办法]
SELECT TOP 1 @vReturnTime=ReturnTime
FROM DBO.car_arrive_info WITH(NOLOCK)
WHERE ListId = @iListId
AND (ReturnTime IS NOT NULL
AND ReturnTime <> '')
SELECT TOP 1 @iiSn=SN
FROM dbo.CAR_ARRIVE_INFO WITH(NOLOCK)
WHERE ListId=@iListId
AND ReturnTime=@vReturnTime)
IF EXISTS (SELECT ReturnTime
FROM dbo.CAR_ARRIVE_INFO WITH(NOLOCK)
WHERE @vArriveTime < @vReturnTime
AND ListId = @iListId
AND SN <@iiSn )
BEGIN
UPDATE dbo.CAR_ARRIVE_INFO
SET ArriveTime = @vArriveTime
,Distnce=@iDistnce
,State=1
,OutTime = @vArriveTime
WHERE ListId = @iListId
AND SN = @iSn
AND State=0
AND SN <@iiSn
AND @vArriveTime < @vReturnTime
END
IF EXISTS (SELECT ReturnTime
FROM dbo.CAR_ARRIVE_INFO WITH(NOLOCK)
WHERE @vArriveTime > @vReturnTime
AND ListId = @iListId AND SN >= @iiSn)
BEGIN
UPDATE dbo.CAR_ARRIVE_INFO
SET ArriveTime = @vArriveTime
,Distnce=@iDistnce
,State=1
,OutTime = @vArriveTime
WHERE ListId = @iListId
AND SN = @iSn
AND State=0
AND SN >=@iiSn
AND @vArriveTime >= @vReturnTime
END
