用一条SQL语句 查询出每门课程都大于80分的学生姓名" 的实现方式

建表:

create table Score(
name varchar(20),
course varchar(20),    
score int)

go

insert Score 
select 'HanMeimei','Chinese',81 union all
select 'HanMeimei','Mathematics',75 union all 
select 'HanMeimei','History',81 union all 
select 'HanMeimei','Geography',75 union all 
select 'LiLei','Chinese',88 union all 
select 'LiLei','Mathematics',90 union all 
select 'JimGreen','Chinese',81 union all 
select 'JimGreen','Mathematics',100 union all 
select 'JimGreen','English',90

 建立索引

create nonclustered index UIX_Score_Score on score(score) include (name)
create nonclustered index UIX_Score_Name on score(name) 
create nonclustered index UIX_Score_Course on score(course) include(name)

　　

方法1:　　找出有科目没有达到80分的姓名,然后过滤

select distinct name 
from score a 
where not exists(select 1 from score where a.name=name and score<80)

select distinct name 
from score 
where name not in (select name from score where score<80)

 

方法2:　　通过分组后过滤的方式

select name
from score 
group by name 
having COUNT(1)=SUM(case when score>=80 then 1 else 0 end)

select name 
from score
group by name 
having MIN(score)>=80

 

 

在记录较少(9条)的情况下  以上四种方式的执行计划(SQL2008)用时比例约为

1:1:1:1

 

更新:

在记录数较多(49995条)的情况下,以上四种方式的执行计划(SQL2008)用时比例为

3:85:6:6