如何通過查詢在sql的日期列中添加循環？

如果ID = 01234這個 ID 在一個月內完成了 3 次交易，如下所示：

***DAY_no              Balance*** 
  1/1/2018              5000
  10/1/2018             10000
  15/1/2018             12000

我想要這樣的數據：

***DAY_no              Balance*** 
  1/1/2018              5000
  2/1/2018              5000
  3/1/2018              5000
  4/1/2018              5000
  5/1/2018              5000
  6/1/2018              5000
  7/1/2018              5000
  8/1/2018              5000
  9/1/2018              5000
  10/1/2018             10000
  11/1/2018             10000
  12/1/2018             10000
  13/1/2018             10000
  14/1/2018             10000
  15/1/2018             12000
  16/1/2018             12000
  17/1/2018             12000
  18/1/2018             12000
  19/1/2018             12000
  20/1/2018             12000
  21/1/2018             12000
  22/1/2018             12000
  23/1/2018             12000
  24/1/2018             12000
  25/1/2018             12000
  26/1/2018             12000
  27/1/2018             12000
  28/1/2018             12000
  29/1/2018             12000
  30/1/2018             12000
  31/1/2018             12000

您可以使用日曆表：

CREATE TABLE Calendar(cDate datetime, cDay int, cDayOfWeek int, cDayName varchar(20), cMonth int);

DECLARE @date date = '20180101';
WHILE @date <= '20180131'
BEGIN
    INSERT INTO Calendar VALUES (@date, 
                                 DAY(@date), 
                                 DATEPART(weekday, @date), 
                                 DATENAME(weekday, @date),
                                 MONTH (@date));
    SET @date = DATEADD(day, 1, @date);
END


CREATE TABLE Mov (Day_no date, Balance int);
INSERT INTO Mov VALUES
('20180101', 5000),
('20180110', 10000),
('20180115', 12000);
GO

受影響的 34 行

SELECT cDAte, (SELECT TOP 1 Balance
               FROM   Mov
               WHERE  Day_no <= cDate
               ORDER BY Day_no DESC) Balance
FROM   Calendar
WHERE cMonth = 1;
GO

日期 | 平衡
:------------------ | ------:
01/01/2018 00:00:00 | 5000
02/01/2018 00:00:00 | 5000
03/01/2018 00:00:00 | 5000
04/01/2018 00:00:00 | 5000
05/01/2018 00:00:00 | 5000
06/01/2018 00:00:00 | 5000
07/01/2018 00:00:00 | 5000
08/01/2018 00:00:00 | 5000
09/01/2018 00:00:00 | 5000
10/01/2018 00:00:00 | 10000
11/01/2018 00:00:00 | 10000
12/01/2018 00:00:00 | 10000
13/01/2018 00:00:00 | 10000
14/01/2018 00:00:00 | 10000
15/01/2018 00:00:00 | 12000
16/01/2018 00:00:00 | 12000
17/01/2018 00:00:00 | 12000
18/01/2018 00:00:00 | 12000
19/01/2018 00:00:00 | 12000
20/01/2018 00:00:00 | 12000
21/01/2018 00:00:00 | 12000
22/01/2018 00:00:00 | 12000
23/01/2018 00:00:00 | 12000
24/01/2018 00:00:00 | 12000
25/01/2018 00:00:00 | 12000
26/01/2018 00:00:00 | 12000
27/01/2018 00:00:00 | 12000
28/01/2018 00:00:00 | 12000
29/01/2018 00:00:00 | 12000
30/01/2018 00:00:00 | 12000
31/01/2018 00:00:00 | 12000

dbfiddle在這裡

您應該始終有一個日曆表來幫助解決此類問題（並且您應該盡量避免將 SQL Server 中的任何內容視為“循環”——它已針對集合進行了優化）。

-- this produces all the days from 2000-01-01 through 2099-12-31
CREATE TABLE dbo.Calendar(d date PRIMARY KEY);

DECLARE @s date = '20000101', @e date = '20991231';

INSERT dbo.Calendar(d) 
 SELECT DATEADD(DAY, r-1, @s)
 FROM ( SELECT TOP (DATEDIFF(DAY, @s, @e)+1)
          r = ROW_NUMBER() OVER (ORDER BY o.[object_id]) 
        FROM sys.all_objects AS o
        CROSS JOIN sys.all_objects AS c
      ) AS x;

一旦你設置了一個日曆表，查詢就可以很容易地LEFT JOIN用來填補這樣的空白。假設您有以下數據：

CREATE TABLE #sample
(
 DAY_no  date, 
 Balance int
);

INSERT #sample(DAY_no,Balance)
VALUES('20180101', 5000 ),
     ('20180110', 10000),
     ('20180115', 12000);

查詢是（我什至使用了一個變數來定義你之後的月份）：

DECLARE @month date = DATEFROMPARTS(2018,1,1);

SELECT DAY_no = c.d,
      MAX(s.Balance)
   FROM dbo.Calendar AS c
   LEFT OUTER JOIN #sample AS s
   ON s.DAY_no <= c.d
   WHERE c.d >= @month
     AND c.d <  DATEADD(MONTH, 1, @month)
   GROUP BY c.d
   ORDER BY c.d;

當然，如果您的第一個數據點在月初之後，您將不得不使查詢更加複雜，因為您需要提取上個月的最後一個已知餘額。

有關日曆表的更多資訊，請參閱此提示。

引用自：https://dba.stackexchange.com/questions/197906