记录个Sql分组使用

----Student
begin transaction

insert 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

----Course
select * from T_Course
begin transaction

insert 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

----Relation
select * from Rel_SutAndCourse

begin transaction

insert 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

--查询出每门课程选课的学生数
select CourseId,count(*) as SutCount from Rel_SutAndCourse
group by CourseId

--查询每个学生选的课的数目
select StuId,count(*) as SutCount from Rel_SutAndCourse
group by StuId

--查询男女同学选课的数量
select stu.sex,Count(distinct rel.stuid) from Rel_SutAndCourse rel
inner join T_Stu stu on rel.StuId=stu.Id
group by stu.sex

--以2014-08-03 19:55为时间分割点，查询哪些同学在时间点之前选课，哪些在时间点之后选了课
select * from Rel_SutAndCourse

select stu.Id,stu.StuName from T_Stu stu 
join Rel_SutAndCourse rel on stu.id=rel.StuId
where rel.Id<=(
select max(rel.Id) from Rel_SutAndCourse rel
where rel.CreateTime<‘2014-08-03 19:55‘
) group by stu.Id,stu.StuName

select stu.Id,stu.StuName from T_Stu stu 
join Rel_SutAndCourse rel on stu.id=rel.StuId
where rel.Id>=(
select min(rel.Id) from Rel_SutAndCourse rel
where rel.CreateTime>‘2014-08-03 19:55‘
) group by stu.Id,stu.StuName