我有一個包含以下欄位的表：
| 線路標識 | 從日期 | 迄今為止 | 數量 |
|---|---|---|---|
| 1 | 01.01.2021 | 10.01.2021 | 100 |
| 2 | 05.07.2021 | 07.07.2021 | 300 |
依此類推，我有 91M 行
我想要一個查詢，它可以幫助我創建一個包含以下資料的視圖：
| 線型 | 從日期 | 數量 |
|---|---|---|
| 1 | 01.01.2021 | 10 |
| 1 | 02.01.2021 | 10 |
| 1 | 03.01.2021 | 10 |
| 1 | 04.01.2021 | 10 |
| 1 | 05.01.2021 | 10 |
| 1 | 06.01.2021 | 10 |
| 1 | 07.01.2021 | 10 |
| 1 | 08.01.2021 | 10 |
| 1 | 09.01.2021 | 10 |
| 1 | 10.01.2021 | 10 |
| 2 | 05.07.2021 | 100 |
| 2 | 06.07.2021 | 100 |
| 2 | 07.07.2021 | 100 |
我不能寫一個存盤程序。只能使用視圖來完成。
提前感謝您的幫助。
uj5u.com熱心網友回復：
所以首先你需要一個計數表（又名數字表。）
;--==== Tally Table
DECLARE @Rows INT = 10;
WITH E1(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(x)),
iTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1, E1 AS b)
SELECT t.N
FROM iTally AS t
WHERE t.N <= @Rows;
這將為您提供所需的行數（本示例最多為 100 行。）
N
------
1
2
3
4
5
6
7
8
9
10
接下來，讓我們確定所需的正確數量和行數：
;--==== 1. Sample Data
DECLARE @t TABLE (
LineId INT,
FromDate DATE,
ToDate DATE,
Amount INT);
INSERT @t VALUES
(1, '01.01.2021', '10.01.2021', 100),
(2, '05.07.2021', '07.07.2021', 300);
;--==== 2. Calculate # of months and divide amount by months for "Amount"
SELECT
LineID = t.LineId,
Months = mo.Months,
Amount = t.Amount/mo.Months
FROM @t AS t
CROSS APPLY (VALUES(DATEDIFF(MONTH,t.FromDate,t.ToDate) 1)) AS mo(Months)
這將回傳：
LineID Months Amount
----------- ----------- -----------
1 10 10
2 3 100
現在我們將使用月數來確定要生成多少行：
;--==== 3. Solution
WITH E1(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(x)),
iTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1, E1 AS b)
SELECT
LineId = t.LineId,
FromDate = DATEADD(MONTH,tally.N-1,t.FromDate),
Amount = t.Amount/mo.Months
FROM @t AS t
CROSS APPLY iTally AS tally
CROSS APPLY (VALUES(DATEDIFF(MONTH,t.FromDate,t.ToDate) 1)) AS mo(Months)
WHERE tally.N <= mo.Months
ORDER BY t.LineId -- Not required;
結果：
LineId FromDate Amount
----------- ---------- -----------
1 2021-01-01 10
1 2021-02-01 10
1 2021-04-01 10
1 2021-05-01 10
1 2021-06-01 10
1 2021-07-01 10
1 2021-08-01 10
1 2021-09-01 10
1 2021-10-01 10
1 2021-03-01 10
2 2021-07-07 100
2 2021-06-07 100
2 2021-05-07 100
作為一個視圖，您可以用真實的臨時表替換我的示例臨時表。
CREATE VIEW dbo.YOUR_VIEW AS
WITH E1(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS x(x)),
iTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E1, E1 AS b)
SELECT
LineId = t.LineId,
FromDate = DATEADD(MONTH,tally.N-1,t.FromDate),
Amount = t.Amount/mo.Months
FROM @t AS t
CROSS APPLY iTally AS tally
CROSS APPLY (VALUES(DATEDIFF(MONTH,t.FromDate,t.ToDate) 1)) AS mo(Months)
WHERE tally.N <= mo.Months
ORDER BY t.LineId -- Not required;
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/425366.html
上一篇：識別隨時間的變化
下一篇：來自SP的T-SQL拆分回應標頭