sql - 按日期累计金额

Question

我们有一个表格，按日期保存累计金额的值，例如:

|       date.      |          |

| 2015-03-01 | 1.23 |

| 2015-03-04 | 1.98 |

| 2015-03-06 | 2.23 |

And we have a table with all dates we need, for instance:

|       date.      |

| 2015-03-01 |

| 2015-03-02 |

| 2015-03-03 |

| 2015-03-04 |

| 2015-03-05 |

| 2015-03-06 |

We would like to get:

|       date.      |          |

| 2015-03-01 | 1.23 |

| 2015-03-02 | 1.23 |

| 2015-03-03 | 1.23 |

| 2015-03-04 | 1.98 |

| 2015-03-05 | 1.98 |

| 2015-03-06 | 2.23 |

是否可以仅使用 SQL 本身？

我很难弄清楚如何做到这一点。

我尝试了一些交叉连接操作，但没有成功。

谢谢

Answer 1

SELECT 
  [date],
  amount
FROM (
  SELECT
    [date],
    amount,
    ROW_NUMBER() OVER(PARTITION BY [date] ORDER BY delta) AS win
  FROM (  
    SELECT 
      dates_table.date AS [date],
      DATEDIFF(TIMESTAMP(dates_table.date), TIMESTAMP(amounts_table.date)) AS delta,
      amount
    FROM amounts_table CROSS JOIN dates_table
    WHERE DATEDIFF(TIMESTAMP(dates_table.date), TIMESTAMP(amounts_table.date)) >= 0
  )
)
WHERE win = 1
ORDER BY 1