Group by集錦
1.group by通常與集合函數(shù)(avg,sum,count,max,min)配合使用
查詢教師表中男女教師的人數(shù)
select count(*) as renshu
from dbo.teacher
group by sex_id
2.針對(duì)多字段的分組
每個(gè)系的男女生的人數(shù)
select count(*) as renshu
from dbo.student
group by sex_id , 系_id
3.多表查詢中的分組應(yīng)用
select count(*) as rs
from dbo.student xs inner join dbo.系 xi
on xs.系_id = xi.系_id
group by 系名
4.對(duì)于計(jì)算字段的分組應(yīng)用
查詢出計(jì)算機(jī)系學(xué)生數(shù)和其他系的學(xué)生數(shù)
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的用法
系人數(shù)在10人以上的系
select  count(*) as shu 系_id
from dbo.student
group by 系_id
having count(*) > 10
或?qū)懗?br /> select *
from (
    select count(*) as shu,系_id
    from dbo.student 
    group by 系_id
)linshi
where linshi.shu > 10
    order by  集錦
1.對(duì)單個(gè)字段的排序(asc升序可省略 desc降序)
select teacher_id
from dbo.teacher
order by teacher_name
2.多字段排序(先按第一個(gè)字段排序，如果相同再按第二個(gè)排序)
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 可按給定的表達(dá)式計(jì)算結(jié)果排序
按教師年齡大小降序排序
select teacher_name
from dbo.teacher
order by DATEDIFF(yy,出生日期,getdate()) desc
將本年入學(xué)的新生排在前面老生排在后面
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合并：
將本校學(xué)生和老師的名字全部查詢出來（去掉重復(fù)）
select teacher_name
from dbo.teacher
union
select student_name
from dbo.student
將本校學(xué)生和老師的名字全部查詢出來（不去掉重復(fù)）
select teacher_name
from dbo.teacher
union all
select student_name
from dbo.student