一些特殊的有用的SQL写法

以ORACLE为例：
1. 查询前3名记录写法：
准备table：
CREATE TABLE "HR"."GRADE"
  (
    "NAME"  VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "SCORE" NUMBER,
    CONSTRAINT "GRADE_PK" PRIMARY KEY ("NAME")
  )
     INSERT INTO GRADE(NAME, SCORE) VALUES('Ming', 99);
     INSERT INTO GRADE(NAME, SCORE) VALUES('Sam', 90);
     INSERT INTO GRADE(NAME, SCORE) VALUES('Will', 87);
     INSERT INTO GRADE(NAME, SCORE) VALUES('Di', 70);
     INSERT INTO GRADE(NAME, SCORE) VALUES('Jason', 52);
     INSERT INTO GRADE(NAME, SCORE) VALUES('Hank', 95);
     INSERT INTO GRADE(NAME, SCORE) VALUES('Joe', 90);
select * from (select * from GRADE order by SCORE desc) where rownum<=3
(说明：如果查询第几名不能用：select * from (select * from GRADE order by SCORE desc) where rownum=2, 实验证明返回0条记录!)
或者：
select * from GRADE g
  where(select count(distinct SCORE) from GRADE where SCORE >= g.SCORE)<=3 order by SCORE desc
2. 查询第3名记录写法：
select * from GRADE g
  where (select count(distinct SCORE) from GRADE where SCORE >= g.SCORE)=3
3. 查询某人的名次：（成绩不能重复）

select count(*) from GRADE where SCORE>=(select SCORE from GRADE where NAME='Will')

4. 查询以某字段为分组中的每组中的最大的记录：

select * from  DOCS k
  where not exists(select * from  DOCS where k.ID=ID and k.VERSION<VERSION)
order by ID;

5. 统计各个分组数量， 如：

a 部门表,  b 员工表
a表字段(
id --部门编号
departmentName-部门名称
)
b表字段(
id--部门编号
employee- 员工名称
)

select count(b.id)as employeecount,a.departmentName from a left join b on a.id=b.id group by b.id,a.departmentName

5. 查询表中重复记录（重复记录是根据单个字段（name）来判断）， 如：

select * from test s where s.name in (select name from test group by name having count(*) > 1);

6. 查询表中重复记录（重复记录是根据多个个字段（name, course）来判断）， 如：

select * from test s where (s.name, s.course) in (select name, course from test group by name, course having count(*) > 1);

7. 删除表中多余的重复记录（多个字段），只留有rowid最小的记录

delete from test a
where (a.name,a.course) in  (select name,course from test group by name,course having count(*) > 1)
and rowid not in (select min(rowid) from test group by name,course having count(*)>1)