SQL CHOOSE(),STRING_SPLIT(),CROSS (OUTER) APPLY使用

介紹幾個挺有用的SQL 函數及 表連接語法

1. CHOOSE()函數-- 返回第1個參數后面，第幾個數組元素（從1開始）
–straight forward function to return the 3rd value

select CHOOSE(3, 'Miller', 'Margheim', 'Galvin', 'Duffy', 'Khanna');

the answer is Galvin

更多：https://www.sqlshack.com/sql-server-choose-function-introduction-and-examples/

2. STRING_SPLIT()–這個很熟悉吧。

 select value from string_split('a,b,c,d,e',',')--將字串按分隔符拆開，并返回一個表，字段名為value

注意適用于：
1）SQL Server 2016 (13.x) and later。
2）Compatibility level 130
STRING_SPLIT requires the compatibility level to be at least 130. When the level is less than 130, SQL Server is unable to find the STRING_SPLIT function.

–應用例子：將每行字符串，轉為將行號，列號的結構。
1)
the sample table

IF OBJECT_ID('tempdb..#mytemptable') IS NOT NULL 
     drop table #MyTempTable
CREATE TABLE #MyTempTable (
    Row# [int] IDENTITY(1,1),
       my_values varchar(100) NULL
);
INSERT INTO  #mytemptable (my_values)
VALUES('Miller,Margheim,Galvin,Duffy,Khanna'),
('apple,orange,grape,melon,banana'),
('red,yellow,blue,green');
select * from #MyTempTable

2)
format the values as a table by rows x columns

IF OBJECT_ID('tempdb..#MyTempValue') IS NOT NULL 
        drop table #MyTempValue
SELECT row#, ROW_NUMBER() OVER(PARTITION BY row# ORDER BY row#) AS "col#", value  
INTO #MyTempValue
FROM #MyTempTable  
    CROSS APPLY STRING_SPLIT(my_values, ',')

3)例如返回第二行，第4個元素

--return the 2nd row 4th column
SELECT * FROM #MyTempValue
WHERE row# = 2
AND col# = 4
>>the answer is melon

3. CROSS APPLY, OUTER APPLY

1. CROSS APPLY

SELECT * FROM tableA CROSS APPLY tableB  --两张表直接连接，無任何的关联条件，返回这两张表的笛卡尔积。相当于：select * from tableA,tableB

SELECT * FROM tableA a CROSS APPLY tableB b
WHERE a.id=b.id ---返回兩張表關聯字段相等的記錄。類似inner join

2. OUTER APPLY

SELECT * FROM tableA a OUT APPLY tableB b
WHERE a.id=b.id ---返回以左邊為基礎，右表如沒有對應的相等字段值，返回NULL值。類似left join

總結：

1. 它們與inner join ,left join 等相比的特別之處, 在于 APPLY ,它會先逻辑计算左表表达式，然后把右表达式应用到左表表达式的每一行.
   象上述面的例子 2.2) , 把左邊查询的列作为参数传递给右邊表值函数。
2. 右表可以是有条件的跟左表的记录匹配，而条件的值可以来至于左表。

再看一個網上舉得很好的例子：(https://www.cnblogs.com/wangzhening6/p/13940914.html)
例：

Ø 有两张表：Student（学生表）和 Score（成绩表），数据如下：

一、 查询每个学生最近两次的考试成绩

1. 先试下 INNER JOIN

1. SQL 代码

SELECT T1.StudentNo, T1.Name, T2.ExamScore, T2.ExamDate FROM Student AS T1

INNER JOIN Score AS T2 ON T1.StudentNo = T2.StudentNo

2. 结果：
   
3. 这不是想要的结果。

2. 再看看 CROSS APPLY

1. SQL 代码

SELECT T1.StudentNo, T1.Name, T2.ExamScore, T2.ExamDate FROM Student AS T1

CROSS APPLY(

    SELECT TOP 2 * FROM Score AS T

    WHERE T1.StudentNo = T.StudentNo

    ORDER BY T.ExamDate DESC

) AS T2

2. 结果：
   
   二、 查询每个学生最近两次的考试成绩,没有参加考试的同学成绩补 null

OUTER APPLY

1. SQL 代码

SELECT T1.StudentNo, T1.Name, T2.ExamScore, T2.ExamDate FROM Student AS T1

OUTER APPLY(

    SELECT TOP 2 * FROM Score AS T

    WHERE T1.StudentNo = T.StudentNo

    ORDER BY T.ExamDate DESC

) AS T2

上面例子總結：

1. CROSS APPLY 的意思是“交叉应用”，在查询时首先查询左表，然后右表的每一条记录跟左表的当前记录进行匹配。匹配成功则将左表与右表的记录合并为一条记录输出；匹配失败则抛弃左表与右表的记录。（与 INNER JOIN 类似）

2. OUTER APPLY 的意思是“外部应用”，与 CROSS APPLY 的原理一致，只是在匹配失败时，左表与右表也将合并为一条记录输出，不过右表的输出字段为 null。（与 LEFT OUTER JOIN 类似）