Group by集锦
1.group by通常与集合函数(avg,sum,count,max,min)配合使用
查询教师表中男女教师的人数
select count(*) as renshu
from dbo.teacher
group by sex_id
2.针对多字段的分组
每个系的男女生的人数
select count(*) as renshu
from dbo.student
group by sex_id , 系_id
3.多表查询中的分组应用
select count(*) as rs
from dbo.student xs inner join dbo.系 xi
on xs.系_id = xi.系_id
group by 系名
4.对于计算字段的分组应用
查询出计算机系学生数和其他系的学生数
select count(*)
(
    case
    when 系_id =1 then 'jsjx'
    else 'qt'
    end
)
from dbo.student
group by
(
    case 
    when 系_id =1 then 'jsjx'
    else 'qt'
    end
)
5.分组条件having的用法
系人数在10人以上的系
select  count(*) as shu 系_id
from dbo.student
group by 系_id
having count(*) > 10
或写成
select *
from (
    select count(*) as shu,系_id
    from dbo.student 
    group by 系_id
)linshi
where linshi.shu > 10
    order by  集锦
1.对单个字段的排序(asc升序可省略 desc降序)
select teacher_id
from dbo.teacher
order by teacher_name
2.多字段排序(先按第一个字段排序，如果相同再按第二个排序)
select *
from dbo.teacher
order by 性别_id , 系_id
3.按笔画排序
select teacher_id
from dbo.teacher
order by teacher_name Collate Chinese_PRC_Stroke_ci_as  desc
4.order by 可按给定的表达式计算结果排序
按教师年龄大小降序排序
select teacher_name
from dbo.teacher
order by DATEDIFF(yy,出生日期,getdate()) desc
将本年入学的新生排在前面老生排在后面
select student_id,届
(    
    case 
    when 届=2006 then 1
    else 0
    end

)
from dbo.student
order by
(
    case 
    when  届 = 2006 then 1
    else 0
    end

) desc
    其他：
union合并：
将本校学生和老师的名字全部查询出来（去掉重复）
select teacher_name
from dbo.teacher
union
select student_name
from dbo.student
将本校学生和老师的名字全部查询出来（不去掉重复）
select teacher_name
from dbo.teacher
union all
select student_name
from dbo.student