前端之家

  1. 首页
  2. 问答

如何动态分配日期？

• 问答

我有一张桌子来存储产品名称，更新日期和付款计划。例如，有一个名为"Domain Hosting"且付款计划为"Monthly"的产品，其续订日期存储在表中为'15-11-2019'，如果我使用Select语句，我希望将其显示为12每月，分为12行，如下所示。

Product Name      Renewal Date   Payment Plan
Domain Hosting    2019-11-15     Monthly
Domain Hosting    2019-12-15     Monthly
Domain Hosting    2020-01-15     Monthly
Domain Hosting    2020-02-15     Monthly
Domain Hosting    2020-03-15     Monthly
Domain Hosting    2020-04-15     Monthly
Domain Hosting    2020-05-15     Monthly
Domain Hosting    2020-06-15     Monthly
Domain Hosting    2020-07-15     Monthly
Domain Hosting    2020-08-15     Monthly
Domain Hosting    2020-09-15     Monthly
Domain Hosting    2020-10-15     Monthly

它不会存储在表中，因此必须在运行时。

jf5437 回答：如何动态分配日期？

这是您的查询，我们使用recursive queries来实现。

  with cte as (
    select 0 as ctr
    union all
    select ctr + 1 from cte where ctr < 11
  ) select 'Domain Hosting' as [Product Name],cast(dateadd(month,ctr,'2019-11-15') as date) as [Renewal Date],'Monthly' as [Payment Plan]
  from cte

输出：

enter image description here

, 
;with AllDates AS
(
  SELECT CAST('2019-11-15' AS DATE) AS Dates
  UNION ALL
  SELECT DateAdd(month,1,Dates)
  FROM AllDates
  WHERE 
  Dates < DateAdd(month,11,CAST('2019-11-15' AS DATE))
)
SELECT 'Domain Hosting' as [Product Name],Dates,'Monthly' as [Payment Plan] FROM AllDates

SQL FIDDLE

enter image description here

sql-server
本文链接：https://www.f2er.com/3154532.html

大家都在问