Apply運算符可以實現(xiàn)兩個查詢結(jié)果的全組合結(jié)果,又稱為交叉集合。例如兩個數(shù)據(jù)組合(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);
插入數(shù)據(jù),腳本如下
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;
結(jié)果如下
可以看出Cross Apply和Outer Apply的區(qū)別
Cross Apply把語句兩邊的兩個Select查詢結(jié)果進行交叉配對,將所有結(jié)果展示出來。Cross Apply查詢確保在查詢兩個子集數(shù)據(jù)的交集時,只有有效信息的集合才被列出來。
OuterApply查詢是把兩個子集的所有組合列了出來,不管數(shù)據(jù)是否有交叉,全部顯示要配對的數(shù)據(jù)。