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

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

在这里插入图片描述

OUTER APPLY

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

在这里插入图片描述

總結：

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

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

? 有两张表：Student（学生表）和 Score（成绩表），数据如下：
在这里插入图片描述
一、查询每个学生最近两次的考试成绩

1. 先试下 INNER JOIN

SQL 代码

SELECT T1.StudentNo, T1.Name, T2.ExamScore, T2.ExamDate FROM Student AS T1INNER JOIN Score AS T2 ON T1.StudentNo = T2.StudentNo

结果：
这不是想要的结果。

2. 再看看 CROSS APPLY

SQL 代码

SELECT T1.StudentNo, T1.Name, T2.ExamScore, T2.ExamDate FROM Student AS T1CROSS APPLY(SELECT TOP 2 * FROM Score AS TWHERE T1.StudentNo = T.StudentNoORDER BY T.ExamDate DESC) AS T2

结果：

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

OUTER APPLY

SQL 代码

SELECT T1.StudentNo, T1.Name, T2.ExamScore, T2.ExamDate FROM Student AS T1OUTER APPLY(SELECT TOP 2 * FROM Score AS TWHERE T1.StudentNo = T.StudentNoORDER BY T.ExamDate DESC) AS T2

在这里插入图片描述
上面例子總結：

CROSS APPLY 的意思是“交叉应用”，在查询时首先查询左表，然后右表的每一条记录跟左表的当前记录进行匹配。匹配成功则将左表与右表的记录合并为一条记录输出；匹配失败则抛弃左表与右表的记录。（与 INNER JOIN 类似）
OUTER APPLY 的意思是“外部应用”，与 CROSS APPLY 的原理一致，只是在匹配失败时，左表与右表也将合并为一条记录输出，不过右表的输出字段为 null。（与 LEFT OUTER JOIN 类似）