如何获得所有科目大于等于80的学生的姓名？

I have the following table

我有下表

Name  |  Subject  | Marks
 --------------------------
 a        M          70
 a        S          80  
 a        L          90
 a        G          100 
 b        M          80
 b        S          90  
 b        L          95
 b        G          100
 c        M          90
 c        G          100

Here I have a "Student" table I want to get the Name of the student who got marks in all subject bigger than and equal "80" from the student table like the following OUTPUT.

在这里，我有一个“学生”表，我希望获得学生姓名，该学生在学生表中获得的所有科目大于等于“80”，如下面的输出。

 Name 
 b
 c

6 个解决方案

#1

A simple aggregation.

一个简单的聚合。

select   name
from     my_table
group by name
having   min(marks) >= 80

In the "real world" you'd be likely to have a separate student table with one row per student, and that might change the logic a little. The best implementation would likely depend on the average number of rows in the scores table per student.

在“现实世界”中，你可能会有一个单独的学生表，每个学生一行，这可能会改变逻辑。最佳实施可能取决于每位学生的分数表中的平均行数。

#2

You have to use GROUP BY clause as follow. This is a classical question helping you to understand GROUP BY and HAVING clauses. GROUP BY realizes an aggregation while HAVING is allowing you to put a condition on it.

您必须使用GROUP BY子句，如下所示。这是一个帮助您理解GROUP BY和HAVING子句的经典问题。 GROUP BY实现聚合，而HAVING允许您为其设置条件。

SELECT name
FROM student
GROUP BY name
HAVING MIN(mark) >= 80

#3

Use a CASE expression to check for each row having more than or equal to 80.

使用CASE表达式检查每行大于或等于80。

Query

询问

select t.Name from(
    select Name, Subject, Marks,
    case when Marks > 79 then 1 else 0 end as mark_status
    from your_table_name
) t
group by t.Name
having count(t.Marks) = sum(t.mark_status);

#4

Select name from table where name not in ( Select distinct name where mark<80);

从不在其中的表中选择名称（选择标记<80的不同名称）;

#5

This should work.

这应该工作。

DECLARE  @Restult TABLE ([Name] VARCHAR(100), [Subject] VARCHAR(100), Marks INT)

INSERT INTO @Restult

 select 'a','M',  70
 union
 select 'a','S',  80
 union  
 select 'a','L',  90
 union
 select 'a','G',  100
 union 
 select 'b','M',  80
 union
 select 'b','S',  90
 union  
 select 'b','L',  95
 union
 select 'b','G',  100
 union
 select 'c','M',  90
 union
 select 'c','G',  100 


;WITH CTE AS
     (
         SELECT [NAME],
                SUM(IIF(r.Marks <80,1, NULL)) AS IsEightyExists             
         FROM   @Restult r
         GROUP BY r.[Name]
     )     



SELECT [Name]
FROM   CTE c
WHERE  IsEightyExists IS NULL

#6

select distinct ss.name from Student ss

从学生s中选择不同的ss.name

join (select name,count(*) q from student group by name)s on s.name=ss.name

在s.name = ss.name上加入（从学生组中选择姓名，计数（*）q）

join (select name,count(*) q from student where marks>=80 group by name)s1 on s1.name=ss.name

加入（选择名称，计数（*）q来自学生，其中标记> = 80按名称分组）s1 on s1.name = ss.name

where s.q=s1.q

其中s.q = s1.q

#1