在实际的场景中,经常有这样的需求:
主表对应的明细表有多条, 但只希望匹配一条记录。
我们当然可以使用 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 小江 江
*/