我有一個問題，我試圖用 t-sql 解決，但我自己無法解決。
我有一個簡單的查詢：
select StartDate, EndDate
from ProductTable
where Site = 'X' and Product_ID = '1'
結果可能如下所示（可以有一行或多行包含開始日期和結束日期）：
| 開始日期 | 結束日期 |
|---|---|
| 2019-06-01 | 2019-09-30 |
| 2019-12-01 | 2020-04-30 |
| 2020-11-30 | 2020-12-31 |
然后我想做的是，對于這個結果集中的每一行，我想創建一個日期之間的月份串列，格式為“yyyymm”，然后將這些串列的結果合并到一個結果集中。
因此，對于第一個結果集中的 3 行，第一步應該給出：
第 1行：201906、201907、201908、201909
第 2行：201912、202001、202002、202003、202004
第 3行：202011 年、202012 年
最終的預期結果當然是：
| 幾個月 |
|---|
| 201906 |
| 201907 |
| 201908 |
| 201909 |
| 201912 |
| 202001 |
| 202002 |
| 202003 |
| 202004 |
| 202011 |
| 202012 |
我已經對 CTE 和游標進行了一些實驗，但我還沒有真正取得任何成功。
有人可以幫我嗎？:-)
uj5u.com熱心網友回復：
如果您有“Tally”或“Nums”功能，這就變成了小游戲。
SELECT Months = CONVERT(CHAR(6),DATEADD(mm,t.N,StartDate),112)
FROM dbo.ProductTable
CROSS APPLY dbo.fnTally(0,DATEDIFF(mm,StartDate,EndDate))t
WHERE Site = 'X'
AND Product_ID = '1'
ORDER BY Site,Product_ID,Months --Just in case we expand on this later.
;
它也像 rCTE 方法那樣消耗 1 次讀取而不是 64 次讀取，這也比 While 回圈慢。
I know a lot of people don't care about that kind of performance for such small sets of data but that's also how they end up with a slow server due to "Death by a Thousand Cuts".
You can search the web for such a function but I can save you some time by posting the link to the one I use. I know the author. :D
Yes, fnTally has fewer total reads, but higher estimates (30% higher, which could affect memory grants at scale), and a higher compile cost. Which of those is more important to you depends on your workload, the size of the product table, the skew in (a) matching rows and (b) max datediff, and your hardware.
Now, you can do this without a recursive CTE, a helper table, or a helper function, but it leads to higher reads:
;WITH m(m) AS
(
SELECT TOP (256) m = ROW_NUMBER() OVER (ORDER BY [object_id])-1
FROM sys.all_objects ORDER BY [object_id]
)
SELECT CONVERT(char(6), DATEADD(MONTH, m.m, p.StartDate), 112)
FROM dbo.ProductTable AS p
INNER JOIN m ON m.m <= DATEDIFF(MONTH, p.StartDate, p.EndDate)
WHERE Product_ID = '1' AND Site = 'X';
You can squeeze those read numbers down by reducing 256 in the TOP clause if you know your datediffs can be < 256 months, but it's hard to get faster than 0. Here are the comparison results:
- Example db<>fiddle
And finally another good resource for number generator functions is this series from Itzik Ben-Gan (work backward and read all the comments).
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/420177.html
標籤：
上一篇：使用CTE更新記錄
下一篇：保存http顫動的回應