我想回圈下面顯示的代碼。我想將該[getpublicholidays]函式使用多年并將它們合并（聯合）。不幸的是，我不知道如何在帶有聯合的回圈中使用該函式。有人可以幫忙嗎？
select * from [getpublicholidays](2019)
UNION
select * from [getpublicholidays](2020)
UNION
select * from [getpublicholidays](2021)
UNION
select * from [getpublicholidays](2022)
uj5u.com熱心網友回復：
您不需要回圈、遞回或臨時表。
只需使用虛擬表來執行此操作，以及CROSS APPLY您的功能
SELECT *
FROM (VALUES
(2019),
(2020),
(2021),
(2022)
) AS Years(Year)
CROSS APPLY getpublicholidays(Years.Year);
如果您想要可變數量的行，您可以使用虛擬數字表
WITH L0 AS (
SELECT n
FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1) ) AS v(n)
),
L1 AS (
SELECT n = 1
FROM L0 AS a, L0 AS b
)
SELECT *
FROM (
SELECT TOP (@endYear - @startYear 1)
@startYear - 1 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM L0
) AS Years(Year)
CROSS APPLY getpublicholidays(Years.Year);
鑒于年份通常只有少量行，您可以硬編碼一個固定數字，然后加入
SELECT *
FROM (VALUES
(2010),(2011),(2012),(2013),(2014),(2015),(2016),(2017),(2018),(2019),
(2020),(2021),(2022),(2023),(2024),(2025),(2026),(2027),(2028),(2029),
(2030),(2031),(2032),(2033),(2034),(2035),(2036),(2037),(2038),(2039)
) AS Years(Year)
CROSS APPLY getpublicholidays(Years.Year);
uj5u.com熱心網友回復：
如果你回圈 X 年，每次回圈到新的一年，你也不需要再聯合其他年份。相反，您需要在某個地方存盤從一年到下一年的結果。也許您正在尋找類似的東西，我只是為您的函式輸出編列，因為我不知道它們到底是什么......相應地調整......
--Create a temp table to match the results of the output of the function.
IF OBJECT_ID('tempdb.dbo.#publicHolidays') IS NOT NULL
DROP TABLE #publicHolidays
CREATE TABLE #publicHolidays(Holiday VARCHAR(28), HolidayYear INT, HolidayDate DATETIME)
--Set a variable for how many years back to include
DECLARE @NumberOfYears INT = 4
--Loop through each year
WHILE @NumberOfYears > 0
BEGIN
DECLARE @Year INT = (SELECT YEAR(GetDate()) - @NumberOfYears 1) --Remove 1 to not include current year
INSERT INTO #publicHolidays (Holiday, HolidayYear, HolidayDate)
SELECT * FROM getpublicholidays(@Year)
SET @NumberOfYears -= 1
END
--Table now has results from each of the 4 years
SELECT * FROM #publicHolidays
IF OBJECT_ID('tempdb.dbo.#publicHolidays') IS NOT NULL
DROP TABLE #publicHolidays
如果這沒有得到你所需要的，那么我們確實需要更多關于你所追求的細節。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/441797.html
上一篇：SQL為每個月創建重復客戶