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)