SQL查询三之聚合函数 

 1 select * from student
 2 
 3 
 4 --查询平均成绩大于70分的系
 5 
 6 select stuDept from student 
 7 
 8 
 9 
10 
11 select stuDept from student
12 group by stuDept
13 having avg(stuAvgrade) > 70
14 --
15 select stuDept,count(*) as 人数 from student
16     group by stuDept
17 --
18 select stuDept, sum(stuAvgrade) from student
19         group by stuDept
20         having sum(stuAvgrade) > 200
21 
22 select stuDept, Max(stuAvgrade) as 最好的一个 from student
23         group by stuDept
24         having Max(stuAvgrade) < 80
25 
26 
27 select stuDept, Min(stuAvgrade) as 最差劲的一个 from student
28         group by stuDept
29         having Min(stuAvgrade) < 60
30 
31 
32 select stuDept, avg(stuAvgrade) as 平均 from student
33 group by stuDept
34 
35 select stuDept as 系,  sum(stuAvgrade) as 总成绩, avg(stuAvgrade) as 平均成绩, max(stuAvgrade) as 最好成绩 from student
36 group by stuDept
37 
38 
39 select * from student
40 
41 --求各系的平均成绩，并且要按从大到小的顺序排
42 select stuDept,  avg(stuAvgrade) as avgrade  from student 
43 group by stuDept
44 order by  avgrade desc
45 
46 select stuDept, avg(stuAvgrade) as 平均成绩 from student
47 group by stuDept
48 order by 平均成绩 desc
49 
50 
51 --求平均成绩最大的系和它的平均成绩
52 
53 
54 
55 select * from student
56 
57 
58 select top 1 stuDept, avg(stuAvgrade) as avgrade from student
59 group by stuDept
60 order by avgrade desc
61 
62 --求平均成绩头两名的系和它的平均成绩
63 
64 select top 2 stuDept,  avg(stuAvgrade) as avgrade  from student 
65 group by stuDept
66 order by  avgrade desc
67 
68 
69 --求平均成绩最大的系和它的平均成绩
70 
71 
72 select stuDept, avg(stuAvgrade) from student
73 group by stuDept
74 having avg(stuAvgrade) = (select top 1 avg(stuAvgrade) as allAvg from student
75 group by stuDept 
76 order by allAvg desc)
77 
78 --查询平均成绩最高的系的学员的所有信息
79     --1.试图直接解决问题
80 
81 select * from student
82     where stuDept = (最好的系)--发现问题转变成求平均成绩最好的系的名字
83 
84     --2求平均成绩最好的系的名字
85 select stuDept from student
86     group by stuDept
87     having avg(stuAvgrade) = (最好成绩) --发现问题转变成求最好平均成绩是什么
88 
89 --同时使用where和having进行条件筛选
90     --并且其平均成绩要大于60的系，并且不能是计算机系
91 select stuDept, avg(stuAvgrade)  from student 
92     where stuDept <> '计算机系'
93     group by stuDept
94     having avg(stuAvgrade) > 60
95     
96     
97 select sum(stuAvgrade) from student

 

posted @ 2018-11-08 15:47  冬夜的火  阅读(370)  评论(0)    收藏  举报