这样的SQL语句你能写出来吗？

Question

创建一个表,如下字段
姓名,年龄,性别,职称,工资
NAME   10
AGE    数字
SEX 字符
DUTY 字符
SALARY  数字CREATE TABLE EMPLOYEE
(
NAME VARCHAR2(10),
AGE INTEGER,
SEX CHAR(1),
DUTY CHAR(1),
SALARY INTEGER
);INSERT INTO  EMPLOYEE VALUES('HOUEN',23,'1','3',5000);
INSERT INTO  EMPLOYEE VALUES('YOKO',21,'0','3',4000);
INSERT INTO  EMPLOYEE VALUES('SKY',28,'1','3',3800);
INSERT INTO  EMPLOYEE VALUES('MARRY',53,'1','2',4000);
INSERT INTO  EMPLOYEE VALUES('JOHN',23,'0','2',2400);
INSERT INTO  EMPLOYEE VALUES('HERY',43,'0','2',1800);
INSERT INTO  EMPLOYEE VALUES('TOM',34,'1','2',5000);
INSERT INTO  EMPLOYEE VALUES('BATDI',22,'1','2',5000);
INSERT INTO  EMPLOYEE VALUES('USEF',23,'0','3',5000);
INSERT INTO  EMPLOYEE VALUES('TFFFE',43,'1','3',5700);
写出统计各类职称中工资最高的三个人的SQL语句

Answer 1

SQL> select * from (select name,duty,salary,row_number() over (partition by duty order by duty,salary desc) ord from empl) where ord<=3;NAME       DUTY                                  SALARY        ORD
---------- ---- --------------------------------------- ----------
TOM        2                                       5000          1
BATDI      2                                       5000          2
MARRY      2                                       4000          3
TFFFE      3                                       5700          1
HOUEN      3                                       5000          2
USEF       3                                       5000          36 rows selected

Answer 2

我把employee改成EMPL了，因为我的数据库已存在EMPLOYEE

Answer 3

select *
 from ( select Name, Age, Sex, Duty, Salary,
               row_number() over(partition by duty order by salary desc) sortid 
        from employee ) DS
 where SortID <= 3