[小菜一碟] 已知有一個固定值的集合,在 SQL Server 怎麼把它變成 Table?

這個標題聽起來像是廢話,我們有一個集合,怎麼把它變成 Table? 啊不就 INSERT INTO 就好了?我要介紹的不是只有 INSERT INTO 而已,我們在寫 SQL 的時候,偶爾會遇到一種場景,我們手上有一個集合,這個集合的值是固定的,比如說年份 2011 ~ 2020、月份 1 ~ 12、...等,這個集合我們會拿去跟現有的資料做 JOIN,進行一些統計運算,我們把這個集合變成 Table,在操作上就會比較方便一點,我就我所知的四種方法來跟大家分享。

Temp Table

第一個要介紹的是 Temp Table,我想這個常寫 SQL 的朋友應該都不陌生,介紹的這四種方法當中也是相對上使用起來最麻煩的。

IF OBJECT_ID('tempdb.dbo.#my_temp_table') IS NOT NULL
    DROP TABLE #my_temp_table
GO

CREATE TABLE #my_temp_table (
    [Year] INT
)

INSERT INTO #my_temp_table([Year])
    VALUES (2011), (2012), (2013), (2014), (2015), (2016), (2017), (2018), (2019), (2020);

SELECT
    *
FROM #my_temp_table mtt

Table Variable

覺得 Temp Table 麻煩的朋友,可以試著改用 Table Variable,它不會建立真實的 Table 物件,在 SQL 語句執行結束時,它的生命週期也跟著結束了。

DECLARE @MyTableVariable AS TABLE (
    [Year] INT
)

INSERT INTO @MyTableVariable([Year])
    VALUES (2011), (2012), (2013), (2014), (2015), (2016), (2017), (2018), (2019), (2020);

SELECT
    *
FROM @MyTableVariable mtv

STRING_SPLIT()

STRING_SPLIT() 字串分割函式,在 SQL Server 2016 之後開始內建,在這個之前我們都自己寫 User Defined Function 來處理字串的分割,但是從 SQL Server 2016 開始後就不用這麼麻煩了。

SELECT
    CONVERT(INT, years_str.[value]) AS [Year]
FROM STRING_SPLIT('2011,2012,2013,2014,2015,2016,2017,2018,2019,2020', ',') years_str

VALUES()

最後一個是 VALUES(),這個函式我們常常接在 INSERT INTO 後面,但其實它可以單獨拿來使用。

SELECT
    *
FROM (VALUES (2011), (2012), (2013), (2014), (2015), (2016), (2017), (2018), (2019), (2020)) AS MyTable ([Year])

以上就我所知道的四種把一個固定值集合變成一個 Table 的方法分享給大家,如果大家還知道其他方法的,還請不吝留言跟我講。

C# 指南ASP.NET 教學ASP.NET MVC 指引
Azure SQL Database 教學SQL Server 教學Xamarin.Forms 教學