sql集锦_20081019_case,group by ,count()等聚合函数,HAVING COUNT(*)>1,inner join,left join

//  http://blog.youkuaiyun.com/lenotang/archive/2008/08/25/2828663.aspx

 

一,

left join table1 b on a.dep=b.dep 划线是连接的条件 group by a.dname

~~~select 后有的字段一般要出现在 group by 中

 

二,

case

        when 条件 then 结果

                when 条件 then 结果    //可以有N个when

        esle  结果

end                        //要有end结束case

 

三,

select N.rq,N.胜,M.负 from

(select rq,胜=count(*) from #tmp where shengfu='胜' group by rq)N

inner join

(select rq,负=count(*) from #tmp where shengfu='负' group by rq)M

on N.rq=M.rq

 

等于

select M.rq,M.胜,N.负
from
(select rq , count(*) as 胜 from tmp1 where shengfu='胜' group by rq)M
inner join
(select rq, count(*) as 负 from tmp1 where shengfu='负' group by rq)N
on N.rq=M.rq

 

1.胜=~~~;负=~~~

2.N;M  标记结果集

 

四,

 

GROUP BY [单位名称]

HAVING COUNT(*)>1     //有记录的~~~

 

group by 字段名 having 组过滤条件

 

select top 2 * from teacher    //只是选取前两条记录

 

五,

 

select teacher.tID , teacher.tName ,count(ts.sID) as '学生人数' from
( teacher left join ts on teacher.tID = ts.tID )
left join student on ts.sID = student.sID
where teacher.tAge >30 and student.sAge >12
group by teacher.tID,teacher.tName

        

1.inner join       相等的记录先出现

表A inner join 表B     =    表B inner join  表A    只不过字段排列先后不同

2. left join      左表全出现,右表相等的就出现

3.right join     右表全出现,左表相等的就出现