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
原文地址:https://blog.csdn.net/a22698488/article/details/140696696
免责声明:本站文章内容转载自网络资源,如本站内容侵犯了原著者的合法权益,可联系本站删除。更多内容请关注自学内容网(zxcms.com)!