sql server 2008中的apply運算符應用辦法。本站提示廣大學習愛好者：（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 null,
Sex bit not null,
Birthday date not null,
Class nvarchar(2) not null,
Grade nvarchar(2) not null,
regdate date not null,
Primary key (id));
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);
個中ScoreInfo中的StudentID為StudentList中id的外鍵
拔出數據，劇本以下
insert into StudentList(Name, Sex, Birthday, Class, Grade, regdate) values('張三', 1, '1988-05-28', 1, 8, '2010-05-05');
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');
兩個表構造樹立終了，數據同樣成功拔出出來了。為了便於講授在StudentList表中再拔出一筆記錄
insert into StudentList(Name, Sex, Birthday, Class, Grade, regdate)
values('李銘', 1, '1989-05-04', 2, 7, '2010-05-05');
輸出以下語句
select * from StudentList a
cross apply
(select ClassID, Score from ScoreInfo where StudentID=a.id) b;
成果以下
再輸出以下語句
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查詢是把兩個子集的一切組合列了出來，不論數據能否有穿插，全體顯示要配對的數據。
