首页 > 代码库 > 记录个Sql分组使用

记录个Sql分组使用

2024-07-15 15:59:26   218人阅读

话说好久没写Sql了，结果和其他小组的同事写Sql的时候遭到鄙视了，看来Sql是永远不能丢的，有时间就要温习一下。

创建3张表：

Rel_SutAndCourse，T_Course，T_Stu

为3张表填充数据：

----Studentbegin transactioninsert T_Stu(StuCode,StuName,Sex) values (‘Stu001‘,‘zhang chu chu‘,1)insert T_Stu(StuCode,StuName,Sex) values (‘Stu002‘,‘li xiao long‘,1)insert T_Stu(StuCode,StuName,Sex) values (‘Stu003‘,‘cheng long‘,1)insert T_Stu(StuCode,StuName,Sex) values (‘Stu004‘,‘liu shi shi‘,0)insert T_Stu(StuCode,StuName,Sex) values (‘Stu005‘,‘yang mi‘,0)insert T_Stu(StuCode,StuName,Sex) values (‘Stu006‘,‘a sa‘,0)insert T_Stu(StuCode,StuName,Sex) values (‘Stu007‘,‘a jiao‘,0)commit transaction
----Courseselect * from T_Coursebegin transactioninsert T_Course(CourseCode,CourseName) values (‘course01‘,‘yu wen‘)insert T_Course(CourseCode,CourseName) values (‘course02‘,‘shu xue‘)insert T_Course(CourseCode,CourseName) values (‘course03‘,‘ying yu‘)insert T_Course(CourseCode,CourseName) values (‘course04‘,‘ti yu‘)commit transaction
----Relationselect * from Rel_SutAndCoursebegin transactioninsert Rel_SutAndCourse(StuId,CourseId,CreateTime) values (1,1,GETDATE())insert Rel_SutAndCourse(StuId,CourseId,CreateTime) values (2,1,GETDATE())insert Rel_SutAndCourse(StuId,CourseId,CreateTime) values (3,2,GETDATE())insert Rel_SutAndCourse(StuId,CourseId,CreateTime) values (3,3,GETDATE())insert Rel_SutAndCourse(StuId,CourseId,CreateTime) values (4,1,GETDATE())insert Rel_SutAndCourse(StuId,CourseId,CreateTime) values (4,2,GETDATE())insert Rel_SutAndCourse(StuId,CourseId,CreateTime) values (4,3,GETDATE())insert Rel_SutAndCourse(StuId,CourseId,CreateTime) values (4,4,GETDATE())insert Rel_SutAndCourse(StuId,CourseId,CreateTime) values (3,1,GETDATE())insert Rel_SutAndCourse(StuId,CourseId,CreateTime) values (5,1,GETDATE())insert Rel_SutAndCourse(StuId,CourseId,CreateTime) values (6,1,GETDATE())insert Rel_SutAndCourse(StuId,CourseId,CreateTime) values (7,1,GETDATE())commit transaction

然后虚拟出3个需求：

--查询出每门课程选课的学生数select CourseId,count(*) as SutCount from Rel_SutAndCoursegroup by CourseId
--查询每个学生选的课的数目select StuId,count(*) as SutCount from Rel_SutAndCoursegroup by StuId
--查询男女同学选课的数量select stu.sex,Count(distinct rel.stuid) from Rel_SutAndCourse relinner join T_Stu stu on rel.StuId=stu.Idgroup by stu.sex
--以2014-08-03 19:55为时间分割点，查询哪些同学在时间点之前选课，哪些在时间点之后选了课select * from Rel_SutAndCourseselect stu.Id,stu.StuName from T_Stu stu join Rel_SutAndCourse rel on stu.id=rel.StuIdwhere rel.Id<=(select max(rel.Id) from Rel_SutAndCourse relwhere rel.CreateTime<‘2014-08-03 19:55‘) group by stu.Id,stu.StuNameselect stu.Id,stu.StuName from T_Stu stu join Rel_SutAndCourse rel on stu.id=rel.StuIdwhere rel.Id>=(select min(rel.Id) from Rel_SutAndCourse relwhere rel.CreateTime>‘2014-08-03 19:55‘) group by stu.Id,stu.StuName

其实就是个简单的分组，一直认为自己的Sql是写的最烂的，看来必须加强啊，争取每次学一些。


联系
我们