sqlserver同一行数据中取下一条记录,简便的方法

方法1,推荐

SELECT 
    a.strWorkguID, 
    a.lngIndex, 
    a.lngMangeID, 
    a.strMangeID, 
    a.dtMangeID, 
    LEAD(lngIndex, 1) OVER (ORDER BY lngIndex) AS lngNextIndex ,
    LEAD(lngMangeID, 1) OVER (ORDER BY lngIndex) AS lngMangeID 

FROM 
    VIEW_HR_PromotionWorkFlow2024 a

WHERE
    a.strWorkguID = '25EEF63F-07E6-44E5-A526-BC2CE0E64619'
    AND a.lngMangeID IS NOT NULL

方法二:

SELECT 
    a.strWorkguID, 
    a.lngIndex, 
    a.lngMangeID, 
    a.strMangeID, 
    a.dtMangeID, 
    isnull(b.lngIndex,5) AS lngNextIndex, 
    isnull(b.lngMangeID,0) AS lngNextMangeID, 
    isnull(b.strMangeID,'已完成') AS strNextMangeID,  
    b.dtMangeID AS dtNextMangeID
FROM 
    VIEW_HR_PromotionWorkFlow2024 a
OUTER APPLY (
    SELECT TOP 1 
        b.lngIndex, 
        b.lngMangeID, 
        b.strMangeID, 
        b.dtMangeID
    FROM 
        VIEW_HR_PromotionWorkFlow2024 b
    WHERE 
        a.strWorkguID = b.strWorkguID 
        AND b.lngIndex > a.lngIndex 
        AND b.lngMangeID IS NOT NULL
    ORDER BY b.lngIndex
) b
WHERE a.lngMangeID IS NOT NULL;
GO

方法三:

SELECT 
    a.strWorkguID, 
    a.lngIndex, 
    a.lngMangeID, 
    a.strMangeID, 
    a.dtMangeID, 
    b.lngIndex AS lngNextIndex, 
    b.lngMangeID AS lngNextMangeID, 
    b.strMangeID AS strNextMangeID,  -- Corrected alias to avoid duplication
    b.dtMangeID AS dtNextMangeID
FROM 
    VIEW_HR_PromotionWorkFlow2024 a
LEFT JOIN 
    VIEW_HR_PromotionWorkFlow2024 b 
    ON a.strWorkguID = b.strWorkguID 
    AND b.lngIndex = (SELECT MIN(b2.lngIndex) 
                      FROM VIEW_HR_PromotionWorkFlow2024 b2 
                      WHERE b2.strWorkguID = a.strWorkguID 
                      AND b2.lngIndex > a.lngIndex 
                      AND b2.lngMangeID IS NOT NULL)
WHERE
    a.strWorkguID = '81d3f331-a6c3-4070-bfe3-4d04a1f86449'
    AND a.lngMangeID IS NOT NULL