关于SQL子查询中列优先的问题
经常用SQL ，有一些小的问题平时不注意也不会发现，下面这个问题是以前碰到过的，觉得有点意思，所以记录在此。
--创始一个班级表,主键是CID,该主键被student表引用为外键约束
create table class( CID int identity , CNo varchar(8),CName varchar(10)
constraint [PK_Class] primary key nonclustered
(
CID asc
) on [primary],
constraint [IX_Class_CNO] unique NONCLUSTERED
(
[cNo] asc
)
) on [primary]
--创建一个学生表,其主键为SNO,外键为CID,引用了Class表中的CID
--注意:student中有一个CNO字段,Class表中也有一个CNo字段
create table student( CID int , SNo varchar(8),SName varchar(10),CNO varchar(8)
constraint [PK_SNO] primary key clustered
(
Sno asc
) on [primary]
) on [primary]
GO
--加入外键引用
alter table student with nocheck add constraint [fk_student_CID] foreign key ([CID])
references class(cid)
表之间的关系如下图
go
--插入班级测试数据
insert into class(cno,cname)
select '0001','班级' union
select '0002','班级' union
select '0003','班级' union
select '0004','班级' union
select '0005','班级'
---插入学生测试数据
insert into student(cid,sno,sname,cNO)
select 1, 'S0001','学生' ,'S0001'union
select 1,'S0002','学生' ,'S0001'union
select 1,'S0003','学生','S0001'union
select 2,'S0004','学生' ,'S0002'union
select 2,'S0005','学生','S0002' union
select 2,'S0006','学生' ,'S0002'union
select 3,'S0007','学生' ,'S0003'union
select 3,'S0008','学生' ,'S0003'union
select 3,'S0009','学生' ,'S0003'union
select 4,'S0010','学生' ,'S0004'union
select 4,'S0011','学生','S0004' union
select 4,'S0012','学生' ,'S0004'union
select 5,'S0013','学生','S0005' union
select 5,'S0014','学生' ,'S0005'union
select 5,'S0015','学生','S0005'
Go
--查看两个表中的所有数据
select * From class
select * From student
--这是一个内连接的测试
select * From dbo.class inner join dbo.student on dbo.class.CID = dbo.student.CID
--这是一个普通的子查询,结果是属于班级的所有学生
select * From dbo.student where CID in (select CID from dbo.class where CName ='班级' )
--这是一个普通的子查询,结果是班级的学生,并且姓名是'学生'
select * From dbo.student where CID in (select CID from dbo.class where CName ='班级' )
and SName ='学生'
--现在将要查询的姓名放在子查询中,查询可以成功吗?注意Class表中并没有SName字段
select * From dbo.student where CID in (select CID from dbo.class
where CName ='班级' and SName ='学生')
--现在更换一个查询的方式,注意Class与Student表中都有CNo字段,但只有Student表中有Sname字段
select * From dbo.student where CID in (select CID from dbo.class
where CNo ='0001' and SName ='学生')
--结果可以看到上面两个查询都成功了,这里的CNo被自动解释为Class表的CNo,而SName又被解释为Student表中的SName
这里是两个查询的执行计划情况：
--看到这里是否觉得有些奇怪,因为子查询中可以引用父查询中的字段,但是当子查询与父查询中具有相同字段时,
--子查询对同名的子段具有优先权
--如果要引用的是父查询中的CNO,应该直接指定,如下面:
select * From dbo.student where CID in (select CID from dbo.class
where student.CNO ='0001' and SName ='学生')
Go
--以下语句会返回错误,指出列名'CNO' 不明确,因为解释器无法判断你的查询要采用哪个表的CNO字段
select * From dbo.class inner join dbo.student on dbo.class.CID = dbo.student.CID
where CNo='0001'
Go
drop table student
drop table class
