sql笔记-group by 统计功能
时间：2021-07-01 10:21:17
帮助过：35人阅读
--查询月销售量小于20的人员销售情况
2 SELECT C.Category,
3 SUM(c.
[Count])
AS CategorySum
4 FROM test.dbo.Category c
5 --WHERE c.Category IN (‘A‘,‘C‘)
6 GROUP BY
7 C.Category
8 HAVING SUM(c.
[Count])
< 20
9 ORDER BY
10 CategorySum
11
12 --查询每门课的最低分
13 SELECT C.Category,
14 MIN(C.
[Count])
AS CategoryMinCount
15 FROM test.dbo.Category c
16 GROUP BY
17 C.Category
18
19
20 --搜索名字相同的学生
21 SELECT s.Sname,
22 COUNT(s.Sname)
23 FROM Stu s
24 GROUP BY
25 s.Sname
26 HAVING COUNT(s.Sname)
> 1
27
28
29 --每个年龄的人数
30 SELECT
31 s.Age
32 ,
COUNT(s.Age)
AS AgeCount
33 FROM test.dbo.Stu s
34 GROUP BY s.Age
35
36 --年龄段统计
37 SELECT t1.AgeRange,
38 COUNT(
*)
AS AgeRangePeopleCount
39 FROM (
40 SELECT Sno,
41 CASE
42 WHEN (age
> 50)
THEN ‘gt100‘
43 WHEN (age
> 20 AND age
<= 50)
THEN ‘20-30‘
44 WHEN (age
<= 20)
THEN ‘10-20‘
45 END AS AgeRange
46 FROM test.dbo.Stu s
47 ) t1
48 GROUP BY
49 t1.AgeRange
sql笔记-group by 统计功能
标签：bsp font pre nbsp 笔记 range 销售 cat group by
