SQL Server使用CROSS APPLY与OUTER APPLY实现连接查询

概述

CROSS APPLY 与 OUTER APPLY 可以做到：
左表一条关联右表多条记录时，我需要控制右表的某一条或多条记录跟左表匹配的情况。

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

一、CROSS APPLY

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

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

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

结果：

二、OUTER APPLY

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

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

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.结合表值函数使用：

有一张表是这样的：

很简单的一张表，就一个字段num，我想把这个字段的int型数字分别转化成二进制八进制和十六进制的数值，有现成的进制转化的表值函数。

SELECT * FROM #T a CROSS APPLY [dbo].[F_TConversion](a.num)

总结一下：如果查询结果集需要用到表值函数对某个字段的值进行处理的话，请使用CROSS APPLY~

2.top子查询的用法：

有一张学生表，分别name，学科，分数 这三个字段，如下：

我要看语文第一名，数学前两名，英语前三名的name，学科，分数，用cross apply实现方法如下:

SELECT b.* FROM (
    select Subject='Chiness',num=1 union all
    select 'Math',2 union all
    select 'English',3) a
cross apply
    (select top(a.num) * from Students where Subject=a.Subject ) b

到此这篇关于SQL Server使用CROSS APPLY与OUTER APPLY实现连接查询的文章就介绍到这了。希望对大家的学习有所帮助，也希望大家多多支持我们。

赞 (0)

相关推荐

  • SQLServer 2008 R2中使用Cross apply统计最新数据和最近数据

    SQLServer 2008 R2中使用Cross apply统计最新数据和最近数据

    使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数.表值函数作为右输入,外部表表达式作为左输入.通过对右输入求值来获得左输入每一行的计算结果,生成的行被组合起来作为最终输出.APPLY 运算符生成的列的列表是左输入中的列集,后跟右输入返回的列的列表. 注意:若要使用 APPLY,数据库兼容级别必须至少为 90. APPLY 有两种形式:CROSS APPLY 和 OUTER APPLY.CROSS APPLY 仅返回外部表中通过表值函数生成结果集的行.OUTER A

  • SQL Server CROSS APPLY和OUTER APPLY的应用详解

    SQL Server数据库操作中,在2005以上的版本新增加了一个APPLY表运算符的功能.新增的APPLY表运算符把右表表达式应用到左表表达式中的每一行.它不像JOIN那样先计算那个表表达式都可以,APPLY必选先逻辑地计算左表达式.这种计算输入的逻辑顺序允许吧右表达式关联到左表表达式. APPLY有两种形式,一个是OUTER APPLY,一个是CROSS APPLY,区别在于指定OUTER,意味着结果集中将包含使右表表达式为空的左表表达式中的行,而指定CROSS,则相反,结果集中不包含使右表

  • SQL Server使用CROSS APPLY与OUTER APPLY实现连接查询

    SQL Server使用CROSS APPLY与OUTER APPLY实现连接查询

    概述 CROSS APPLY 与 OUTER APPLY 可以做到:左表一条关联右表多条记录时,我需要控制右表的某一条或多条记录跟左表匹配的情况. 有两张表:Student(学生表)和 Score(成绩表),数据如下: 一.CROSS APPLY ROSS APPLY 的意思是“交叉应用”,在查询时首先查询左表,,然后右表的每一条记录跟左表的当前记录进行匹配.匹配成功则将左表与右表的记录合并为一条记录输出:匹配失败则抛弃左表与右表的记录.(与 INNER JOIN 类似) 查询每个学生最近两次的

  • SQL Server 2012使用Offset/Fetch Next实现分页数据查询

    SQL Server 2012使用Offset/Fetch Next实现分页数据查询

    在Sql Server 2012之前,实现分页主要是使用ROW_NUMBER(),在SQL Server2012,可以使用Offset ...Rows  Fetch Next ... Rows only的方式去实现分页数据查询. select [column1] ,[column2] ... ,[columnN] from [tableName] order by [columnM] offset (pageIndex-1)*pageSize rows fetch next pageSize r

  • SQL Server复制需要有实际的服务器名称才能连接到服务器

    SQL Server复制需要有实际的服务器名称才能连接到服务器

    服务器上安装的WIN2008 R2,然后没有在意机器名,安装了SQL2008 R2数据库之后,配置AD域的时候修改了机器名. 然后,开始配置数据库镜像同步的时候,先试了下数据库复制发布,结果提示"SQL Server 复制需要有实际的服务器名称才能连接到服务器.不支持通过服务器别名.IP 地址或任何其他备用名称进行连接.请指定实际的服务器名称...",如下图所示. 网上查了下,有同学总结说是机器名不对,这才想起来之前确实修改过机器名,只是本次链接数据库中的实例用的是IP或别名形式的地址

  • 简析SQL Server数据库用视图来处理复杂的数据查询关系

    简析SQL Server数据库用视图来处理复杂的数据查询关系

    SQL Server数据库用视图来处理复杂的数据查询关系是本文我们主要要介绍的内容,该内容是这样想到的:在辅助教务系统那块的时候,我做的一个页面是对单个老师和整个学院老师的工作量查询,这个操作设计到了三个本数据库中的表和一个不同数据库中的一个教师信息表,如果用普通的SQL语句是非常难实现的,由于我刚开始做的视频播放系统,数据库的表相对比较少,没有涉及到这么复杂的处理关系,刚开始感觉很难. 后来想到用视图可以解决多个表的复杂关系,但是另外一张表是不同数据库的,是否依然能进行操作,经过测试之后,居然

  • SQL Server 复制需要有实际的服务器名称才能连接到服务器

    今天在做sql Server 2005的实验的时候碰到的问题,问题描述很清楚,怀疑是我以前给计算机修改了名称而导致的.可以用select @@servername和select serverproperty ('servername')对照一下,两个的结果是否一样,不一样就会出现问题,以下给出解决的SQL脚本 复制代码 代码如下: if serverproperty('servername') <> @@servername begin declare @server sysname set

  • 通过SQL Server的位运算功能巧妙解决多选查询方法

    无论使用int还是varchar,对于Status的多选查询都是不易应对的.举例,常规思维下对CustomerStatus的Enum设置如下: 复制代码 代码如下: [Serializable] public enum CustomerStatus { New = 0, Active = 1, Overdue = 2, Suspended = 3, Closing = 4, Closed = 5 } 在数据库中以int形式存储了Status值. 如果我在页面中想一次搜索状态为Active,Ove

  • sql server 2008中的apply运算符使用方法

    sql server 2008中的apply运算符使用方法

    Apply运算符可以实现两个查询结果的全组合结果,又称为交叉集合.例如两个数据组合(A,B).(A,B),他们的交叉集合为(AA,AB,AA,AB). Apply分为Cross Apply和Outer Apply两种使用方式.具体分析如下: 首先先建立两个表StudentList和ScoreInfo.脚本语言如下: 复制代码 代码如下: create table StudentList(id int Identity(1,1) not null,Name nvarchar(20) not nul

  • sql server关键字详解大全（图文）

    sql server关键字详解大全（图文）

    下面把sqlserver中cross apply和outer apply关键字具体介绍展示如下: 1.CROSS APPLY 和OUTER APPLY MSDN解释如下(个人理解不是很清晰): 使用 APPLY 运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数.表值函数作为右输入,外部表表达式作为左输入. 通过对右输入求值来获得左输入每一行的计算结果,生成的行被组合起来作为最终输出. APPLY 运算符生成的列的列表是左输入中的列集,后跟右输入返回的列的列表. APPLY 有两种形

  • SQL Server学习笔记之事务、锁定、阻塞、死锁用法详解

    本文实例讲述了SQL Server学习笔记之事务.锁定.阻塞.死锁用法.分享给大家供大家参考,具体如下: 1.事务 隐式事务 /*================================================================== 当以create,drop, fetch,open, revoke,grand, alter table,select,insert,delete,update,truncate table 语句首先执行的时候,SQL Server会话

随机推荐