Use an SQL statement to find out how to achieve a student's name of more than 80 points per course

Build table:

1 Create TableScore (2Namevarchar( -),3Coursevarchar( -),    4Scoreint)5 6 Go7 8 Insertscore9 Select 'Hanmeimei','Chinese',Bayi Union  AllTen Select 'Hanmeimei','Mathematics', the Union  All  One Select 'Hanmeimei',' History',Bayi Union  All  A Select 'Hanmeimei','Geography', the Union  All  - Select 'Lilei','Chinese', the Union  All  - Select 'Lilei','Mathematics', - Union  All  the Select 'Jimgreen','Chinese',Bayi Union  All  - Select 'Jimgreen','Mathematics', - Union  All  - Select 'Jimgreen','中文版', -

Method 1: Find the name that has the subject not reaching 80 points, and then filter

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

1 Select distinct    2 from 3 where not in (select  from where score<)

Method 2: Filter by group after the way

1 Selectname2  fromscore3 Group  byname4  having COUNT(1)=SUM( Case  whenScore>= the  Then 1 Else 0 End)

1 Select  2from score3Group by 4  havingMIN(score)>=

Results:

Name

Jimgreen
Lilei

In the case of fewer records (9), the ratio of the execution plan (SQL2008) of the above four methods is

14:14:11:11

Use an SQL statement to find out how to achieve a student's name of more than 80 points per course