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 stdname SQL语句： SELECT s1.year "year",(SELECT SUM(s2.salary) FROM Tab s2 WHERE s2.year<=s1.year) "salary" From Tab s1 ORDER BY s1.year SQL语句： 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 rq SQL语句： select (case when a>b then a else b end ),(case when b>c then b esle c end) from table_name SQL语句： select * from tb where datediff(dd,SendTime,getdate())=0 SQL语句： 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 from t_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) where rn>9980; 一个比较高效的分页语句 select * from (select a.*,rownum rn from tb_user a where rownum <= 4) where rn >= 2