if not object_id('cj')is null
drop table cj
go
create table cj(stuName nvarchar(10),KCM nvarchar(10),cj numeric(5,2))
insert into cj select '张三','语文',98
union select '李四','语文',89
union select '王五','语文',67
union select '周攻','语文',56
union select '张三','数学',89
union select '李四','数学',78
union select '王五','数学',90
union select '周攻','数学',87
select stuname,kcm,cj from cj a
where not exists(select 1 from cj b where
(select count(*) from cj where b.kcm=a.kcm and b.stuname!=a.stuname and b.cj>a.cj)>1)
go//找每门课程第一名的select stuname,kcm,cj from cj a
where not exists(select 1 from cj b where
(select count(*) from cj where kcm=a.kcm and stuname!=a.stuname and cj>a.cj)>1)
go//
我测试了和上面的结果不同不知道两个语句差异在哪呢?
drop table cj
go
create table cj(stuName nvarchar(10),KCM nvarchar(10),cj numeric(5,2))
insert into cj select '张三','语文',98
union select '李四','语文',89
union select '王五','语文',67
union select '周攻','语文',56
union select '张三','数学',89
union select '李四','数学',78
union select '王五','数学',90
union select '周攻','数学',87
select stuname,kcm,cj from cj a
where not exists(select 1 from cj b where
(select count(*) from cj where b.kcm=a.kcm and b.stuname!=a.stuname and b.cj>a.cj)>1)
go//找每门课程第一名的select stuname,kcm,cj from cj a
where not exists(select 1 from cj b where
(select count(*) from cj where kcm=a.kcm and stuname!=a.stuname and cj>a.cj)>1)
go//
我测试了和上面的结果不同不知道两个语句差异在哪呢?
(select count(*) from cj where B.kcm=a.kcm and B.stuname!=a.stuname and cj>a.cj)>1)
select stuname,kcm,cj from cj a
where not exists(select 1 from cj b where
(select count(*) from cj where kcm=a.kcm and stuname!=a.stuname and cj>a.cj)>1)红色部分是与最外面的比较 而上面一句是与嵌套连的CJ比较
select stuname,kcm,cj from cj a
where not exists(
select 1 from cj b where
(select count(*) from cj where kcm=a.kcm and stuname!=a.stuname and cj>a.cj)
>=1
)
===
> 1 修改成 >=1
想问它是怎样的执行顺序