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
