厦门美发店,安雅 三级,统考英语
apply运算符可以实现两个查询结果的全组合结果,又称为交叉集合。例如两个数据组合(a,b)、(a,b),他们的交叉集合为(aa,ab,aa,ab)。
apply分为cross apply和outer apply两种使用方式。具体分析如下:
首先先建立两个表studentlist和scoreinfo。脚本语言如下:
create table scoreinfo(
id int identity(1,1) not null primary key,
studentid int not null,
classid int not null,
score int not null,
testdate date not null,
regdate date not null);
插入数据,脚本如下
insert into studentlist(name, sex, birthday, class, grade, regdate) values('李四', 1, '1985-09-13', 4, 4, '2010-05-05');
insert into studentlist(name, sex, birthday, class, grade, regdate) values('王丽', 0, '1987-11-05', 1, 7, '2010-05-05');
insert into scoreinfo(studentid, classid, score, testdate, regdate) values(1, 1, 98, '2010-04-15', '2010-05-01');
insert into scoreinfo(studentid, classid, score, testdate, regdate) values(1, 2, 92, '2010-04-15', '2010-05-01');
insert into scoreinfo(studentid, classid, score, testdate, regdate) values(1, 3, 86, '2010-04-15', '2010-05-01');
insert into scoreinfo(studentid, classid, score, testdate, regdate) values(2, 1, 95, '2010-04-15', '2010-05-01');
insert into scoreinfo(studentid, classid, score, testdate, regdate) values(2, 2, 94, '2010-04-15', '2010-05-01');
insert into scoreinfo(studentid, classid, score, testdate, regdate) values(2, 3, 91, '2010-04-15', '2010-05-01');
insert into scoreinfo(studentid, classid, score, testdate, regdate) values(3, 1, 90, '2010-04-15', '2010-05-01');
insert into scoreinfo(studentid, classid, score, testdate, regdate) values(3, 2, 88, '2010-04-15', '2010-05-01');
insert into scoreinfo(studentid, classid, score, testdate, regdate) values(3, 3, 90, '2010-04-15', '2010-05-01');
再输入以下语句
select * from studentlist a
outer apply
(select classid, score from scoreinfo where studentid=a.id) b;
结果如下
可以看出cross apply和outer apply的区别
cross apply把语句两边的两个select查询结果进行交叉配对,将所有结果展示出来。cross apply查询确保在查询两个子集数据的交集时,只有有效信息的集合才被列出来。
outerapply查询是把两个子集的所有组合列了出来,不管数据是否有交叉,全部显示要配对的数据。
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论