数据库进阶-数据处理

Question

SQL server有以下两个表。分别为表A，表B
表A：

Line	Model	Year	Month	Nums
A	aa	2024	1月	154
A	aa	2024	2月	123

表B：

Line	Model	Year	Month	ChangeDay	AfterNums
AA	aa	2024	2月	2024-02-04	123

SQL server中，将这两个表组合关联处理获取到以下的表C内容：

Line	Model	Day	TZ
A	aa	2024-01-01	154
A	aa	2024-01-02	154
A	aa	2024-01-03	154
A	aa	2024-01-04	154
A	aa	2024-01-05	154
A	aa	2024-01-06	154
A	aa	2024-01-07	154
A	aa	2024-01-08	154
A	aa	2024-01-09	154
A	aa	2024-01-10	154
。	。	。	。
A	aa	2024-02-03	154
A	aa	2024-02-04	123
A	aa	2024-02-04	124
A	aa	2024-02-04	125
。	。	。	。

表C中的。表示按照规律直到表B的日期出现，则表C的TZ值变更为表B的AfterNums值，再次按照日期逐天的规律直到下一个表B的内容出现。表A和表B可通过Model连接。问：在SQL server中，如何将这表A、表B组合关联处理获取到表C内容？

Answer 1

随、风 上午好☀️☀️☀️️
本答案参考ChatGPT-3.5

解决方案：

1. 首先，我们需要使用UNION ALL操作符将表A和表B合并成一个新的临时表。通过将表A和表B的列进行对齐，我们可以使用 NULL 值填充缺失的列。

   SELECT Line, Model, Year, Month, Nums AS TZ, NULL AS ChangeDay, NULL AS AfterNums
   FROM 表A
   UNION ALL
   SELECT Line, Model, Year, Month, NULL AS TZ, ChangeDay, AfterNums
   FROM 表B
   

2. 接下来，我们可以使用窗口函数ROW_NUMBER()来分配一个唯一的标识符给每一行，并按照日期进行排序。

   SELECT *, ROW_NUMBER() OVER (ORDER BY Year, Month, ChangeDay) AS rn
   INTO #temp
   FROM (
       SELECT Line, Model, Year, Month, Nums AS TZ, NULL AS ChangeDay, NULL AS AfterNums
       FROM 表A
       UNION ALL
       SELECT Line, Model, Year, Month, NULL AS TZ, ChangeDay, AfterNums
       FROM 表B
   ) t
   

3. 现在，我们可以使用窗口函数LAG()来获取前一行的AfterNums的值，并将其应用到当前行的TZ列，从而实现根据规律更新TZ的值。

   SELECT Line, Model, DATEADD(DAY, DATEDIFF(DAY, 0, DATEADD(DAY, -1, COALESCE(ChangeDay, LAG(ChangeDay) OVER (ORDER BY Year, Month, ChangeDay)))), 0) AS Day, COALESCE(AfterNums, LAG(AfterNums) OVER (ORDER BY Year, Month, ChangeDay)) AS TZ
   FROM #temp
   

4. 最后，我们可以根据需要删除临时表。

   DROP TABLE IF EXISTS #temp
   

以上就是在SQL Server中将表A和表B进行组合关联处理并获取到表C内容的方法。

注意：这里假设表A和表B的数据是按照日期有序的。如果不是按照日期有序的，可以在联接操作前使用ORDER BY子句对表A和表B进行排序。