TSQL -从下一个相邻行填充记录
TSQL -从下一个相邻行填充记录
提问于 2012-12-06 23:31:03
电流输出
Activity FromTime ToTime
-------------------------------------------------------------------
STOPPED INSIDE POI 2012-11-14 01:08:46.000 2012-11-14 01:19:46.000
MOVING INSIDE POI 2012-11-14 01:20:46.000 2012-11-14 01:21:46.000
MOVING OUTSIDE POI 2012-11-14 01:22:46.000 2012-11-14 01:22:46.000
STOPPED OUTSIDE POI 2012-11-14 01:23:46.000 2012-11-14 01:23:46.000
MOVING OUTSIDE POI 2012-11-14 01:24:46.000 2012-11-14 01:25:46.000预期输出
Activity FromTime ToTime
-------------------------------------------------------------------
STOPPED INSIDE POI 2012-11-14 01:08:46.000 2012-11-14 01:20:46.000
MOVING INSIDE POI 2012-11-14 01:20:46.000 2012-11-14 01:22:46.000
MOVING OUTSIDE POI 2012-11-14 01:22:46.000 2012-11-14 01:23:46.000
STOPPED OUTSIDE POI 2012-11-14 01:23:46.000 2012-11-14 01:24:46.000
MOVING OUTSIDE POI 2012-11-14 01:24:46.000 2012-11-14 01:25:46.000有什么简单的方法可以做到这一点吗？
回答 2
Stack Overflow用户
回答已采纳
发布于 2012-12-06 23:42:51
这对你来说应该是可行的：
UPDATE table1
SET ToTime = join_table.newToTime
FROM (
SELECT t2.FromTime target, MIN(t1.FromTime) newToTime
FROM table1 t1
JOIN table1 t2 ON t1.FromTime > t2.FromTime
GROUP BY t2.FromTime) join_table
WHERE FromTime = join_table.target基本上，您正在寻找大于当前行的最小FromTime -并将其用作ToTime的新值。请注意，最后一行不会被更新，因为找不到匹配的更大的FromTime。
Stack Overflow用户
发布于 2012-12-06 23:46:44
;with a as
(
select Activity, FromTime, ToTime,
row_number() over (order by FromTime) rn from <yourtable>
)
select a.Activity, a.FromTime, coalesce(b.FromTime, a.ToTime) ToTime
from a left join a b on a.rn = b.rn - 1
order by a.rn页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/13746975
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号