如何根据以前的日期在sql中创建新列?

Question

Col A         
1 Jan
2 Jan
3 Jan
5 Jan
6 Jan

输出

Col A      Col B
1 Jan    NULL
2 Jan    1 Jan
3 Jan    2 Jan
5 Jan    3 Jan
6 Jan    5 Jan

Answer 1

您可以尝试使用@Akina建议的LAG()

WITH D AS (
SELECT A FROM YOUR_TABLE 
) 
SELECT A, LAG(A, 1) OVER(ORDER BY A DESC)
FROM D

EDIT我看到你现在在mysql中，你也可以试试这个答案

Answer 2

使用窗口函数LAG()

SELECT *,
LAG(col_a) OVER (ORDER BY col_a)
FROM t;

Demo:db<>fidldle

Answer 3

WITH D AS (从表中查询日期(SELECT date, LAG(date, 1) OVER(ORDER BY date)从D