如何用SQL查询填充stop列空值为下一行start值？是否需PHP？

当然可以仅通过SQL查询完成，完全不需要借助PHP等后端语言处理数据，而且实现方式非常简洁——利用SQL的窗口函数LEAD()就能轻松搞定。

核心思路 #

你的需求是把stop列的NULL值替换为同一用户下一行的start值，LEAD()函数正好能帮我们获取当前行之后指定偏移量的行数据，完美匹配这个场景。

具体SQL代码 #

假设你的表名为user_periods，可以用以下查询：

SELECT
  start,
  COALESCE(stop, LEAD(start) OVER (PARTITION BY name ORDER BY start)) AS stop,
  name
FROM user_periods
ORDER BY start;

代码解释 #

• LEAD(start) OVER (PARTITION BY name ORDER BY start)：
  • PARTITION BY name：确保我们只在同一个用户的行内查找下一行（如果你的数据有多个用户，这个分组逻辑很关键）；
  • ORDER BY start：保证行是按start从小到大排序的，这样取到的下一行start才是正确的后续时间段起始值；
  • LEAD(start)：直接获取当前行的下一行的start值。
• COALESCE(stop, ...)：如果当前行的stop不是NULL，就保留原值；如果是NULL，就替换成LEAD()取到的下一行start值。
• 最后一行的stop会保留NULL，因为它没有下一行可以取值，正好符合你想要的结果。

测试结果 #

用你给出的测试数据运行这个查询，会得到完全符合预期的结果：

内容的提问来源于stack exchange，提问作者Zbyszek Kisły