数据库中关于分组操作详解

最新推荐文章于 2023-01-10 17:50:40 发布

原创最新推荐文章于 2023-01-10 17:50:40 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

SqlServer2008 同时被 2 个专栏收录

37 篇文章

订阅专栏

SqlServer2005

20 篇文章

订阅专栏

USE [TeachingLib]
GO
/****** Object: Table [dbo].[tb_Student]    Script Date: 10/28/2013 22:56:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb_Student](
[stuID] [int] IDENTITY(1,1) NOT NULL,
[stuName] [varchar](50) NULL,
[stuSex] [nchar](10) NULL,
[stuAge] [int] NULL,
[Course] [nvarchar](50) NULL,
[Score] [int] NULL,
CONSTRAINT [PK_tb_Student] PRIMARY KEY CLUSTERED
(
[stuID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[tb_Student] ON
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (1, N'易日媛', N'女         ', 22, N'计算机及应用', 78)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (2, N'徐杉', N'男         ', 21, N'计算机管理', 86)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (3, N'徐一伟', N'男         ', 24, N'英语', 78)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (4, N'艾佳宇', N'女         ', 22, N'高等数学', 97)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (5, N'何雨', N'女         ', 23, N'软件开发工具', 86)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (6, N'催墨', N'男         ', 20, N'高等数学', 67)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (7, N'郑梅', N'女         ', 23, N'计算机管理', 80)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (8, N'白玉芬', N'女         ', 25, N'计算机及应用', 90)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (9, N'岳元倩', N'女         ', 22, N'计算机网络', 68)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (10, N'刘晨', N'男         ', 26, N'大学语文', 82)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (11, N'周易', N'男         ', 24, N'大学语文', 83)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (12, N'沈然', N'男         ', 20, N'计算机原理', 92)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (13, N'沈然', N'男         ', 20, N'大学语文', 70)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (14, N'刘晨', N'男         ', 26, N'计算机原理', 59)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (15, N'郑梅', N'女         ', 23, N'高等数学', 80)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (16, N'徐杉', N'男         ', 21, N'英语', 90)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (17, N'岳元倩', N'女         ', 22, N'软件开发工具', 66)
INSERT [dbo].[tb_Student] ([stuID], [stuName], [stuSex], [stuAge], [Course], [Score]) VALUES (18, N'周易', N'男         ', 24, N'计算机网络', 100)
SET IDENTITY_INSERT [dbo].[tb_Student] OFF

--查询出所有学生信息：---------------------------------------------
select *
from tb_Student

--查询出实际学生数量:----------------------------------------------
select COUNT(distinct stuName) as 人数
from tb_Student

--查询出每个学生的成绩总和：显示学生姓名,门数,总成绩.--------------
select stuName,count(Course)as courseNum,SUM(score) as totalScore,avg(score)平均成绩,MAX(Score)最高成绩,MIN(Score)最低成绩
from tb_Student
group by stuID,stuName

--查询出所有女生,男生人数,总成绩:----------------------------------
select stuSex 性别,COUNT(*)人数,SUM(score)成绩,avg(score)平均成绩,MAX(Score)最高成绩,MIN(Score)最低成绩
from tb_Student
group by stuSex

--查询出所有男生人数,总成绩:---------------------------------------
select stuSex 性别,COUNT(*)人数,SUM(Score)总成绩,avg(score)平均成绩,MAX(Score)最高成绩,MIN(Score)最低成绩
from tb_Student
group by stuSex
having stuSex='女'

--查询出各个年龄段的课程数，成绩总和：-----------------------------
select stuAge 年龄,count(*)人数,SUM(score)总成绩,avg(score)平均成绩,MAX(Score)最高成绩,MIN(Score)最低成绩
from tb_Student
group by stuAge

--查询出各科成绩总和,平均成绩,最高分,最低分：----------------------
select Course ,SUM(score)总成绩,AVG(score)平均成绩,MAX(score)最高分,min(score)最低分
from tb_Student
group by Course

--查询出各科成绩中大于该科平均成绩的所有学生信息：-----------------
select *
from tb_Student a
where score>(
Select avg(score)平均成绩 from tb_Student b group by b.Course having b.Course=a.Course
)

--查询出各科成绩中最高分的学生信息：---------------------------------

--1)查询出各科中最高分:----------------------------------------------
select Course,MAX(score)最高分
from tb_Student
group by Course

--科目成绩

--大学语文  83
--高等数学  97
--计算机管理 86
--计算机及应用 90
--计算机网络 100
--计算机原理 92
--软件开发工具 86
--英语   90

--2)将查询出的结果集插入到声明的表中：--------------------------------
declare @tem table
(
course nvarchar(50),
score int
)
insert @tem
select Course,MAX(score)最高分
from tb_Student
group by Course

--3)将具有数据的临时表连接原表，查询出符合条件的记录：

------------------写法一(最优):----------------------------------------
--select a.*
--from tb_Student a inner join @tem b
--on a.Course=b.course and a.Score=b.score

------------------写法二(同写法一):------------------------------------
--select a.*
--from tb_Student a,@tem b
--where a.Course=b.course and a.Score=b.score

------------------写法三:这个方法在几十条、几百条还可以，如果几千条，几万条记录时，效率不如上面两种。

--select a.*
--from tb_Student a
--where a.Score in
-- (
--  select b.score
--  from @tem b
--  where a.Course=b.course and a.Score=b.score
-- )

--4)查询出想要的结果：-------------------------------------------------

--编号姓名性别年龄科目成绩

--2  徐杉男         21 计算机管理  86
--4  艾佳宇女         22 高等数学  97
--5  何雨女         23 软件开发工具 86
--8  白玉芬女         25 计算机及应用 90
--11 周易男         24 大学语文  83
--12 沈然男         20 计算机原理  92
--16 徐杉男         21 英语   90
--18 周易男         24 计算机网络  100

------------------------------------------------------------------------
--查询出各科中最高分的女同学的所有信息(要求是女性)：

select a.*
from tb_Student a,@tem b
where a.Score=b.score and a.Course=b.course and a.stuSex='女' --可以换成'男',则会查询出男同学的所有信息。

-------------------------------------------------------------------------
--查询出学生中总成绩最大的记录---------------------------------------------

--这里需要说明一下：先是执行from语句，再执行group by，再执行select，再执行order by，再去重(没有就不执行),再执行top关键字，最后查询结果。

select top 1 stuName ,SUM(score)totalScore
from tb_Student
group by stuName
order by totalScore desc

-------------------------------------------------------------------------

--查询出学生中总成绩最大的那个学生的所有信息-----------------------------
declare @temTable table
(
stuName nvarchar(50),
totalScore int
)
insert into @temTable
select top 1 stuName ,SUM(score)totalScore
from tb_Student
group by stuName
order by totalScore desc

select a.*
from tb_Student a,@temTable b
where a.stuName=b.stuName
-------------------------------------------------------------------------
--ALL关键字在group by中的使用：
--1)ALL关键字显示那些被WHERE子句限制的组;
--2)ALL关键字不会显示那些被HAVING子句限制的组;

select Course ,AVG(score) 平均分
from tb_Student a
where stuAge>23
group by Course

--查询结果如下：

大学语文 82
计算机及应用 90
计算机网络 100
计算机原理 59
英语 78

select Course ,AVG(score) 平均分
from tb_Student a
where stuAge>23
group by ALL Course

--查询结果如下：

大学语文 82
高等数学 NULL
计算机管理 NULL
计算机及应用 90
计算机网络 100
计算机原理 59
软件开发工具 NULL
英语 78
-------------------------------------------------------------------------
--CUBE、ROLLUP关键字在group by中的使用-----------------------------------
--1)CUBE、ROLLUP关键字不能和ALL关键字一起使用。
--2)原因：ALL关键字会显示不满足WHERE条件的组，而这些组对应的汇总信息不是有效值，
-- 因此CUBE、ROLLUP无法对其进行汇总。

--当一个分组字段的时候两者没区别：指定在结果集内不仅包含由GROUP BY提供的行,还包含汇总行-----------------------------------------
--rollup关键字的使用：-------------------------------------------------------------------
select stuAge, Course,AVG(score) 平均分
from tb_Student a
group by stuAge,Course with rollup

20 大学语文 70
20 高等数学 67
20 计算机原理 92
20 NULL  76
21 计算机管理 86
21 英语  90
21 NULL  88
22 高等数学 97
22 计算机及应用78
22 计算机网络 68
22 软件开发工具66
22 NULL  77
23 高等数学 80
23 计算机管理 80
23 软件开发工具86
23 NULL  82
24 大学语文 83
24 计算机网络 100
24 英语  78
24 NULL  87
25 计算机及应用90
25 NULL  90
26 大学语文 82
26 计算机原理 59
26 NULL  70
NULL NULL 80

--cube关键字的使用：-----------------------------------------------------
select stuAge,Course,avg(score) 平均分
from tb_Student a
group by stuAge,Course with cube

20  大学语文 70
24  大学语文 83
26  大学语文 82
NULL 大学语文 78
20  高等数学 67
22  高等数学 97
23  高等数学 80
NULL 高等数学 81
21  计算机管理 86
23  计算机管理 80
NULL 计算机管理 83
22  计算机及应用78
25  计算机及应用90
NULL 计算机及应用84
22  计算机网络 68
24  计算机网络 100
NULL 计算机网络 84
20  计算机原理 92
26  计算机原理 59
NULL 计算机原理 75
22  软件开发工具66
23  软件开发工具86
NULL 软件开发工具76
21   英语 90
24   英语 78
NULL  英语 84
NULL  NULL 80
20   NULL 76
21   NULL 88
22   NULL 77
23   NULL 82
24   NULL 87
25   NULL 90
26   NULL 70

--注意:
--1)WITH ROLLUP关键字主要对GROUP BY子句中列出的第一个分组字段进行汇总计算。
--2)GROUP BY子句如果有两个字段，那么字段位置不同，返回的结果集也不同。

---------------------------------------------------------------------

--compute 关键字的使用-----------------------------------------------
--针对所有的：-------------------------------------------------------
select *
from tb_Student
compute count(stuSex),avg(score)

--针对排序后的分组计算：---------------------------------------------
select *
from tb_Student
order by stuSex --这个地方可以有多个字段排序.
compute count(stuSex),avg(score)
by stuSex --这个地方必须包含在排序依据中.