我想在两个问题上得到帮助:a)有多少学生以一流的成绩毕业?b) 有多少学生获得了优异成绩

Question

根据学生获得的分数对他们进行评分如下：

40-50  - Second class
50-60  - First Class
60-80  - First Class
80-100 - Distinctions.

上述查询的表Stud如下所示：

ID|Name     |Marks                                                                                                                                                                           
11|Britney  | 95    
12|Dyana    | 55    
13|Jenny    | 66    
14|Christene| 88    
15|Meera    | 24    
16|Priya    | 76    
17|Priyanka | 77    
18|Paige    | 74    
19|Samantha | 87    
21|Julia    | 96    
27|Evil     | 79    
29|Jane     | 64    
31|Scarlet  | 80    
32|Kristeen |100    
34|Fanny    | 75    
37|Belvet   | 78
38|Danny    | 75

我尝试创建成绩表，首先将成绩分配给下面的查询表：

Select from stud Grade=Case 
when marks>100 then 'Distinction' 
when 80>70 and marks<100 then 'Distinction' 
when marks>60 and marks<80 then 'First Class' 
when marks>50 and marks<60 then 'First Class' 
when marks>40 and marks<50 then 'Second Class' 
when marks<40 then 'Fail' 
else 'No Grade Available' end Grade ; 

Answer 1

Select count(*) res from stud where marks between 80 and 100

结果将具有"0"；res"；获得80至100 分数的学生人数

Answer 2

当标记值与要分配给有问题的组的条件匹配时，我会使用一个恰好为1的计数器的总和，使用CASE WHEN表达式：

WITH
-- your input, don't use in real query ..
indata(ID,Name,Marks) AS (
SELECT 11,'Britney',95
UNION ALL SELECT 12,'Dyana',55
UNION ALL SELECT 13,'Jenny',66
UNION ALL SELECT 14,'Christene',88
UNION ALL SELECT 15,'Meera',24
UNION ALL SELECT 16,'Priya',76
UNION ALL SELECT 17,'Priyanka',77
UNION ALL SELECT 18,'Paige',74
UNION ALL SELECT 19,'Samantha',87
UNION ALL SELECT 21,'Julia',96
UNION ALL SELECT 27,'Evil',79
UNION ALL SELECT 29,'Jane',64
UNION ALL SELECT 31,'Scarlet',80
UNION ALL SELECT 32,'Kristeen',100
UNION ALL SELECT 34,'Fanny',75
UNION ALL SELECT 37,'Belvet',78
UNION ALL SELECT 38,'Danny',75
)
-- real query starts here
SELECT
SUM(CASE WHEN marks > 50 AND marks <= 80 THEN 1 END) AS first_class_count
, SUM(CASE WHEN marks > 80                 THEN 1 END) AS distinction_count
FROM indata
-- out  first_class_count | distinction_count 
-- out -------------------+-------------------
-- out                 11 |                 5

Answer 3

您可以在select语句中定义类别，并通过在组中使用它来获得更易于阅读的结果。此外，请确保正确定义边界。以下是的示例

SELECT CASE WHEN marks BETWEEN 81 AND 100 then 'Distinction' 
WHEN marks BETWEEN 51 AND 80 then 'First Class' 
WHEN marks BETWEEN 40 AND 50 then 'Second class'  
ELSE 'No Grade Available' 
END Grade,
COUNT(*) AS stud_count
FROM stud 
WHERE marks > 50   
GROUP BY CASE WHEN marks BETWEEN 81 AND 100 then 'Distinction' 
WHEN marks BETWEEN 51 AND 80 then 'First Class' 
WHEN marks BETWEEN 40 AND 50 then 'Second class'  
ELSE 'No Grade Available' 
END

SQL Fiddle