我有一个表(SQL Server 2017),其中包含日期范围内的数据。
+---------+----------+------------+---------------+-------------+
| good_id | store_id | promo_name | date_id_begin | date_id_end |
+---------+----------+------------+---------------+-------------+
| 1122 | 42 | promo_1 | 2020-02-01 | 2020-02-05 |
+---------+----------+------------+---------------+-------------+
| 2244 | 41 | promo_2 | 2020-03-01 | 2020-03-03 |
+---------+----------+------------+---------------+-------------+
我需要在此期间每天获取此数据。
+---------+----------+------------+---------------+
| good_id | store_id | promo_name | date_id_begin |
+---------+----------+------------+---------------+
| 1122 | 42 | promo_1 | 2020-02-01 |
+---------+----------+------------+---------------+
| 1122 | 42 | promo_1 | 2020-02-02 |
+---------+----------+------------+---------------+
| 1122 | 42 | promo_1 | 2020-02-03 |
+---------+----------+------------+---------------+
| 1122 | 42 | promo_1 | 2020-02-04 |
+---------+----------+------------+---------------+
| 1122 | 42 | promo_1 | 2020-02-05 |
+---------+----------+------------+---------------+
| 2244 | 41 | promo_2 | 2020-03-01 |
+---------+----------+------------+---------------+
| 2244 | 41 | promo_2 | 2020-03-02 |
+---------+----------+------------+---------------+
| 2244 | 41 | promo_2 | 2020-03-03 |
+---------+----------+------------+---------------+
我可以从循环内的某个范围提取日期,但是如何选择其他信息(good_id,store_id,promo_name)?
如果您没有日历或理货表格,则可以使用临时理货表格。
例
Select A.[good_id]
,A.[store_id]
,A.[promo_name]
,[Date] = B.D
From YourTable A
Join (
Select Top (25000) D=DateAdd(DAY,Row_Number() Over (Order By (Select Null)),'1999-12-31') From master..spt_values n1,master..spt_values n2
) B on D between date_id_begin and date_id_end
退货
good_id store_id promo_name Date
1122 42 promo_1 2020-02-01
1122 42 promo_1 2020-02-02
1122 42 promo_1 2020-02-03
1122 42 promo_1 2020-02-04
1122 42 promo_1 2020-02-05
2244 41 promo_2 2020-03-01
2244 41 promo_2 2020-03-02
2244 41 promo_2 2020-03-03
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句