--创建学生表
create table Students(
sno nvarchar(10) not null primary key,
name nvarchar(30) not null,
gender nchar(1) check(gender = '男' or gender='女') default('男')
)
go
insert into Students(sno,name,gender) values('S001','张三','男')
insert into Students(sno,name,gender) values('S002','李四','男')
insert into Students(sno,name,gender) values('S003','王五','女')
insert into Students(sno,name,gender) values('S004','赵六','女')
go
--创建课程表
create table Course(
cno nvarchar(10) not null primary key,
name nvarchar(30) not null,
)
go
insert into Course(cno,name)values('C001','数学')
insert into Course(cno,name)values('C002','语文')
insert into Course(cno,name)values('C003','英语')
insert into Course(cno,name)values('C004','物理')
go
--创建成绩
create table Score(
sno nvarchar(10) not null,
cno nvarchar(30) not null,
score int
)
go
insert into Score values ('S001','C001',91);
insert into Score values ('S002','C001',93);
insert into Score values ('S002','C002',94);
insert into Score values ('S002','C003',95);
insert into Score values ('S003','C001',96);
insert into Score values ('S003','C002',97);
insert into Score values ('S003','C003',98);
--为了测试full join,特意插入一条学号不存在的数据
insert into Score values ('XXXX','C003',98);
/***************************************************************inner join*******************************************************/
--查询学生的学号，姓名，对应的课程名称，以及分数
select ST.sno,ST.name,CO.name,SC.score
from Students ST
inner join Score SC on ST.sno = SC.sno
inner join Course CO on SC.cno = CO.cno
sno name name score
---------- ------------------------------ ------------------------------ -----------
S001 张三 数学 91
S002 李四 数学 93
S002 李四 语文 94
S002 李四 英语 95
S003 王五 数学 96
S003 王五 语文 97
S003 王五 英语 98
(7 行受影响)
/***************************************************************right join*******************************************************/
--查询所有学生对应的课程信息
select ST.name,SC.cno from Students ST
left join Score SC on ST.sno=SC.sno
/***************************************************************left join*******************************************************/
select ST.name,SC.cno from Score SC
right join Students ST on ST.sno=SC.sno
name cno
------------------------------ ------------------------------
张三 C001
李四 C001
李四 C002
李四 C003
王五 C001
王五 C002
王五 C003
赵六 NULL
(8 行受影响)
/***************************************************************full join*******************************************************/
--查询所有的学生以及分数信息
select * from Students ST
full join Score SC on ST.sno=SC.sno
--outer可以省略，等同于如下查询
select * from Students ST full outer join Score SC on ST.sno=SC.sno
sno name gender sno cno score
---------- ------------------------------ ------ ---------- ------------------------------ -----------
S001 张三 男 S001 C001 91
S002 李四 男 S002 C001 93
S002 李四 男 S002 C002 94
S002 李四 男 S002 C003 95
S003 王五 女 S003 C001 96
S003 王五 女 S003 C002 97
S003 王五 女 S003 C003 98
S004 赵六 女 NULL NULL NULL
NULL NULL NULL XXXX C003 98
(9 行受影响)
/***************************************************************cross join(笛卡尔积)*******************************************************/
--查询“缺考”的学生信息(有课程，无成绩的信息)
--利用cross join，生成学生与课程的笛卡尔积，也就是所有学生对应的所有课程信息
--然后跟成绩表做左联接，如果成绩不存在，则说明“缺考”
select *
from (select ST.sno, ST.name as Sname, CO.cno, CO.name as CName
from Students ST
cross join Course CO) T
left join Score SC on T.cno = SC.cno
and T.sno = SC.sno
where SC.cno is null
sno Sname cno CName sno cno score
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ -----------
S001 张三 C002 语文 NULL NULL NULL
S001 张三 C003 英语 NULL NULL NULL
S001 张三 C004 物理 NULL NULL NULL
S002 李四 C004 物理 NULL NULL NULL
S003 王五 C004 物理 NULL NULL NULL
S004 赵六 C001 数学 NULL NULL NULL
S004 赵六 C002 语文 NULL NULL NULL
S004 赵六 C003 英语 NULL NULL NULL
S004 赵六 C004 物理 NULL NULL NULL
(9 行受影响)
--cross join：指定两个表的叉积。返回相同的行
--这里的cross join就是笛卡尔积
select ST.sno, ST.name as Sname, CO.cno, CO.name as CName
from Students ST
cross join Course CO
--或者是不指定连接条件，查询出来的也是笛卡尔积
select ST.sno, ST.name as Sname, CO.cno, CO.name as CName
from Students ST, Course CO
--或者是cross apply
select ST.sno, ST.name as Sname, CO.cno, CO.name as CName
from Students ST
cross apply Course CO
/***************************************************************Apply运算符*******************************************************/
--APPLY 运算符的左操作数和右操作数都是表表达式。
--这些操作数之间的主要区别是，right_table_source 可以使用表值函数，从 left_table_source 获取一个列作为函数的参数之一。
--left_table_source 可以包括表值函数，但不能以来自 right_table_source 的列作为参数。
--查询学生分数最高的两科成绩
select ST.name, T.cno, T.score
from Students ST
cross apply (select top 2 *
from Score SC
where ST.sno = SC.sno
order by SC.score desc) T
name cno score
------------------------------ ------------------------------ -----------
张三 C001 91
李四 C003 95
李四 C002 94
王五 C003 98
王五 C002 97
(5 行受影响)
--查询所有学生分数最高的两科成绩，成绩少于两科或者没有成绩的，显示为空
select ST.name, isnull(T.cno,'不存在') as 课程号, isnull(T.score,0) as 分数
from Students ST
outer apply (select top 2 *
from Score SC
where ST.sno = SC.sno
order by SC.score desc) T
name 课程号 分数
------------------------------ ------------------------------ -----------
张三 C001 91
李四 C003 95
李四 C002 94
王五 C003 98
王五 C002 97
赵六 不存在 0
(6 行受影响)
通俗地说，cross apply 与表或者是表值函数连接时，右边的表或者表示函数依赖于左边的结果集
就类似于在外层遍历左边的表，
依次获取左边的表中的数据作为参数，传递到右边的查询中（或者函数中）作为查询条件，得到的结果集再与外层的结果集做连接，然后输出结果
咋一看，跟普通的join没啥区别，要注意的是：普通的表之间的关联是根据关联字段，只要关联字段满足条件，就输出
而cross apply右边的结果集是根据左边每一行数据的关联条件来做输入的，
把左边结果的一行中的某个字段，作为参数，获取一个结果集，让这个结果集跟左边的表做连接
其实根据上面的查询，很容易理解，查询出每个人分数最高的两门课程的成绩，
就是遍历学生信息表中的“每个人”（SNO）
------->作为参数传递到cross（outer） apply子查询中
------->得到成绩的结果集
------->跟学生信息表关联，输出
其中，cross apply 和 outer apply的区别就类似于inner join与left join
决定了是否输出左边结果集中存在，右边结果集中不存在数据的情况下，是否输出的问题