SQL

SQL语句：SELECT stdname,IsNull(Max(CASE stdsubject WHEN '化学' THEN result end),0) [化学],IsNull(Max(CASE stdsubject WHEN '数学' THEN result end),0) [数学],IsNull(Max(CASE stdsubject WHEN '物理' THEN result end),0) [物理],IsNull(Max(CASE stdsubject WHEN '语文' THEN result end),0) [语文]FROM student GROUP BY stdnameSQL语句：SELECT s1.year "year",(SELECT SUM(s2.salary) FROM Tab s2 WHERE s2.year<=s1.year) "salary" From Tab s1 ORDER BY s1.yearSQL语句：Select * From T Where T.Id In (Select MIN(T.Id) From T Group By T.Name)SQL语句：select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rqSQL语句：select (case when a>b then a else b end ),(case when b>c then b esle c end) from table_nameSQL语句：select * from tb where datediff(dd,SendTime,getdate())=0SQL语句：select a.* from 表 a where 日期=(select min(日期) from 表 where 单据=a.单据)SQL语句：select dept,count(*) from employee group by dept; SQL语句：select dept,count(*) from employee group by dept having count(*)>1; SQL语句：select e.first_name,salary,d.name from s_emp e, s_dept d where e.dept_id = d.id and salary > 1200; SQL语句：select stu.sno, stu.sname from student stu, course c, sc where stu.sno = sc.sno and sc.cno = c.cno and c.cname='JAVA';SQL语句：select stu.sno, stu.sname from student stu where (select count(*) from sc where sno=stu.sno) = 5;SQL语句：delete from employee where rowid not in (select max(t1.rowid) from employee t1 group by t1.emp_id,t1.emp_name,t1.salary);--这里用min(rowid)也可以。SQL语句：select * from (select rownum num, s_emp.* from s_emp) where num >=1 and num <= 5; SQL语句：1.根据ROWID来分select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from(select rowid rid,cid fromt_xiaoxi order by cid desc) where rownum<10000) where rn>9980) order by cid desc;2.按ROWNUM来分select * from(select t.*,rownum rn from(select * from t_xiaoxi order by cid desc) t where rownum<10000) wherern>9980;一个比较高效的分页语句select * from (select a.*,rownum rn from tb_user a where rownum <= 4) where rn >= 2