Outer Apply 和 Cross Apply 的妙用

吉普赛的歌 2019-06-07 07:42:52

1195

分类专栏： SQL Server

最后发布:2019-06-07 07:42:52首次发布:2019-06-07 07:42:52

本文链接：https://blog.csdn.net/yenange/article/details/91125451

版权

在实际的场景中，经常有这样的需求：

主表对应的明细表有多条，但只希望匹配一条记录。

我们当然可以使用 row_number 取 rid =1 来实现，但某些情况下 outer apply 或 cross apply 或许会更方便。

USE tempdb
GO
IF OBJECT_ID('stu') IS NOT NULL
DROP TABLE stu
GO
CREATE TABLE stu(
	stuId INT PRIMARY KEY,
	stuName NVARCHAR(5) NOT NULL	
)
GO
IF OBJECT_ID('score') IS NOT NULL
DROP TABLE score
GO
CREATE TABLE score(
	scoreId INT IDENTITY(1,1) PRIMARY KEY,
	stuId INT NOT NULL,
	course NVARCHAR(5) NOT NULL,
	score INT NOT NULL	
)
GO
SET NOCOUNT ON
INSERT INTO stu(stuId, stuName) VALUES (1,'小明')
INSERT INTO stu(stuId, stuName) VALUES (2,'小华')
INSERT INTO stu(stuId, stuName) VALUES (3,'小江')

INSERT INTO score (stuId,course,score) VALUES (1,'语文',90)
INSERT INTO score (stuId,course,score) VALUES (1,'数学',88)
INSERT INTO score (stuId,course,score) VALUES (1,'英语',96)
INSERT INTO score (stuId,course,score) VALUES (2,'语文',80)
GO

--统计出所有学生的 他本人所有成绩之中的 最高分， 没有成绩也要显示
SELECT * 
FROM stu 
	OUTER APPLY (
		SELECT TOP 1 * FROM score WHERE stu.stuId=score.stuId ORDER BY score DESC 
	) AS ff
/*
stuId       stuName scoreId     stuId       course score
----------- ------- ----------- ----------- ------ -----------
1           小明      3           1           英语     96
2           小华      4           2           语文     80
3           小江      NULL        NULL        NULL   NULL
*/

--统计出所有学生的 他本人所有成绩之中的 最高分， 没有成绩不要显示
SELECT * 
FROM stu 
	CROSS APPLY (
		SELECT TOP 1 * FROM score WHERE stu.stuId=score.stuId ORDER BY score DESC 
	) AS ff

/*
stuId       stuName scoreId     stuId       course score
----------- ------- ----------- ----------- ------ -----------
1           小明      3           1           英语     96
2           小华      4           2           语文     80
*/


/*
--CROSS APPLY 用于函数的范例
--string_split 只能用于 SQL Server 2016+
SELECT * FROM stu CROSS APPLY string_split( stuff(stu.stuName,2,0,'.'),'.') AS ff

stuId       stuName value
----------- ------- ------
1           小明      小
1           小明      明
2           小华      小
2           小华      华
3           小江      小
3           小江      江
*/