--创建表
create table emp
(
empno int ,
ename varchar(10) ,
job varchar(9) ,
mgr int,
hierdate datetime ,
sal decimal(7,2),
comm decimal(7,2),
deptno int,
)create table dept
(
deptno int unique,
dname varchar(14),
loc varchar(13),
)create table salgrade
(
gread int,
losal int,
hisal int,
)create table bonus
(
ename varchar(10),
job varchar(9),
sal int,
comm int,
)--插入数据
insert into emp values (7369,'smith','clerk',7902,'1980/12/17',800.00,null,20)
insert into emp values (7499,'allen','salesman',7698,'1981/2/20',1600.00,300.00,30)
insert into emp values (7521,'ward','salesman',7698,'1981/2/22',1250.00,500.00,30)
insert into emp values (7566,'jones','manager',7839,'1981/4/2',2975.00,null,20)
insert into emp values (7654,'martin','salesman',7698,'1981/9/28',1250.00,1400.00,30)
insert into emp values (7698,'blake','manager',7839,'1981/5/1',2850.00,null,30)
insert into emp values (7782,'clark','manager',7839,'1987/6/9',2450.00,null,10)
insert into emp values (7788,'socctt','analyst',7566,'1987/4/19',3000.00,null,20)
insert into emp values (7844,'turner','salesman',7698,'1981/9/8',1500.00,0.00,30)
insert into emp values (7876,'adams','clerk',7788,'1987/5/23',1100.00,null,20)
insert into emp values (7900,'james','clerk',7698,'1981/12/3',950.00,null,30)
insert into emp values (7902,'foro','analyst',7566,'1981/1/23',3000.00,null,10)
insert into emp values (7934,'miller','clerk',7782,'1982/1/23',1300.00,null,10)
select * from empinsert into dept values(10,'accounting','new york')
insert into dept values(20,'research','dallas')
insert into dept values(30,'sales','chicago')
insert into dept values(40,'operations','boston')
select * from deptinsert into salgrade values(1,700,1200)
insert into salgrade values(2,1201,1400)
insert into salgrade values(3,1401,2000)
insert into salgrade values(4,2001,3000)
insert into salgrade values(5,3001,9999)
select * from salgrade表:emp
NO 字段 类型 描述
1 empno Number(4) 雇员编号
2 ename Varchar2(10) 雇员姓名
3 job Varchar2(9) 工作职位
4 mgr Number(4) 雇员的领导(经理)编号
5 hierdate date 入职日期
6 sal Number(7,2) 月薪/工资
7 comm Number(7,2) 奖金
8 deptno Number(2) 雇员所属部门的编号表:dept
NO 字段 类型 描述
1 deptno Number(2) 部门编号(唯一)
2 dname Varchar2(14) 部门名称
3 loc Varchar2(13) 地址表:salgrade
NO 字段 类型 描述
1 Grade number 等级名称
2 Losal number 此等级的最低工资
3 hisal number 此等级的最高工资
上面是表结构
create table emp
(
empno int ,
ename varchar(10) ,
job varchar(9) ,
mgr int,
hierdate datetime ,
sal decimal(7,2),
comm decimal(7,2),
deptno int,
)create table dept
(
deptno int unique,
dname varchar(14),
loc varchar(13),
)create table salgrade
(
gread int,
losal int,
hisal int,
)create table bonus
(
ename varchar(10),
job varchar(9),
sal int,
comm int,
)--插入数据
insert into emp values (7369,'smith','clerk',7902,'1980/12/17',800.00,null,20)
insert into emp values (7499,'allen','salesman',7698,'1981/2/20',1600.00,300.00,30)
insert into emp values (7521,'ward','salesman',7698,'1981/2/22',1250.00,500.00,30)
insert into emp values (7566,'jones','manager',7839,'1981/4/2',2975.00,null,20)
insert into emp values (7654,'martin','salesman',7698,'1981/9/28',1250.00,1400.00,30)
insert into emp values (7698,'blake','manager',7839,'1981/5/1',2850.00,null,30)
insert into emp values (7782,'clark','manager',7839,'1987/6/9',2450.00,null,10)
insert into emp values (7788,'socctt','analyst',7566,'1987/4/19',3000.00,null,20)
insert into emp values (7844,'turner','salesman',7698,'1981/9/8',1500.00,0.00,30)
insert into emp values (7876,'adams','clerk',7788,'1987/5/23',1100.00,null,20)
insert into emp values (7900,'james','clerk',7698,'1981/12/3',950.00,null,30)
insert into emp values (7902,'foro','analyst',7566,'1981/1/23',3000.00,null,10)
insert into emp values (7934,'miller','clerk',7782,'1982/1/23',1300.00,null,10)
select * from empinsert into dept values(10,'accounting','new york')
insert into dept values(20,'research','dallas')
insert into dept values(30,'sales','chicago')
insert into dept values(40,'operations','boston')
select * from deptinsert into salgrade values(1,700,1200)
insert into salgrade values(2,1201,1400)
insert into salgrade values(3,1401,2000)
insert into salgrade values(4,2001,3000)
insert into salgrade values(5,3001,9999)
select * from salgrade表:emp
NO 字段 类型 描述
1 empno Number(4) 雇员编号
2 ename Varchar2(10) 雇员姓名
3 job Varchar2(9) 工作职位
4 mgr Number(4) 雇员的领导(经理)编号
5 hierdate date 入职日期
6 sal Number(7,2) 月薪/工资
7 comm Number(7,2) 奖金
8 deptno Number(2) 雇员所属部门的编号表:dept
NO 字段 类型 描述
1 deptno Number(2) 部门编号(唯一)
2 dname Varchar2(14) 部门名称
3 loc Varchar2(13) 地址表:salgrade
NO 字段 类型 描述
1 Grade number 等级名称
2 Losal number 此等级的最低工资
3 hisal number 此等级的最高工资
上面是表结构
(select ename,deptno,dname,
row_number() over(partition by a.deptno order by sal desc) rn
from emp a,dept b
where a.deptno=b.deptno) t
where rn<=2
SELECT *
FROM dept A
CROSS APPLY(
SELECT TOP 2 ename,sal+isnull(comm,0) salall
FROM emp B
WHERE A.deptno=B.deptno
ORDER BY sal+isnull(comm,0) DESC
) B
/*
deptno dname loc ename salall
----------- -------------- ------------- ---------- ---------------------------------------
10 accounting new york foro 3000.00
10 accounting new york clark 2450.00
20 research dallas socctt 3000.00
20 research dallas jones 2975.00
30 sales chicago blake 2850.00
30 sales chicago martin 2650.00(6 行受影响)*/
--2K:
SELECT A.*,B.ename,B.sal+isnull(B.comm,0) salall
FROM dept A
JOIN emp B
ON A.deptno=B.deptno
WHERE B.sal+isnull(B.comm,0) IN(
SELECT TOP 2 sal+isnull(comm,0)
FROM emp
WHERE deptno=A.deptno
ORDER BY sal+isnull(comm,0) DESC
)
ORDER BY 1,salall DESC
/*
deptno dname loc ename salall
----------- -------------- ------------- ---------- ---------------------------------------
10 accounting new york foro 3000.00
10 accounting new york clark 2450.00
20 research dallas socctt 3000.00
20 research dallas jones 2975.00
30 sales chicago blake 2850.00
30 sales chicago martin 2650.00(6 行受影响)*/
create table emp
(
empno int ,
ename varchar(10) ,
job varchar(9) ,
mgr int,
hierdate datetime ,
sal decimal(7,2),
comm decimal(7,2),
deptno int,
)create table dept
(
deptno int unique,
dname varchar(14),
loc varchar(13),
)create table salgrade
(
gread int,
losal int,
hisal int,
)create table bonus
(
ename varchar(10),
job varchar(9),
sal int,
comm int,
)--插入数据
insert into emp values (7369,'smith','clerk',7902,'1980/12/17',800.00,null,20)
insert into emp values (7499,'allen','salesman',7698,'1981/2/20',1600.00,300.00,30)
insert into emp values (7521,'ward','salesman',7698,'1981/2/22',1250.00,500.00,30)
insert into emp values (7566,'jones','manager',7839,'1981/4/2',2975.00,null,20)
insert into emp values (7654,'martin','salesman',7698,'1981/9/28',1250.00,1400.00,30)
insert into emp values (7698,'blake','manager',7839,'1981/5/1',2850.00,null,30)
insert into emp values (7782,'clark','manager',7839,'1987/6/9',2450.00,null,10)
insert into emp values (7788,'socctt','analyst',7566,'1987/4/19',3000.00,null,20)
insert into emp values (7844,'turner','salesman',7698,'1981/9/8',1500.00,0.00,30)
insert into emp values (7876,'adams','clerk',7788,'1987/5/23',1100.00,null,20)
insert into emp values (7900,'james','clerk',7698,'1981/12/3',950.00,null,30)
insert into emp values (7902,'foro','analyst',7566,'1981/1/23',3000.00,null,10)
insert into emp values (7934,'miller','clerk',7782,'1982/1/23',1300.00,null,10)
select * from empinsert into dept values(10,'accounting','new york')
insert into dept values(20,'research','dallas')
insert into dept values(30,'sales','chicago')
insert into dept values(40,'operations','boston')
select * from deptinsert into salgrade values(1,700,1200)
insert into salgrade values(2,1201,1400)
insert into salgrade values(3,1401,2000)
insert into salgrade values(4,2001,3000)
insert into salgrade values(5,3001,9999)
select * from salgrade
WITH romance
AS
(
SELECT a.empno,a.ename,a.sal,b.deptno ,
rn=row_number()OVER(PARTITION BY a.deptno ORDER BY sal DESC )
FROM emp a
LEFT JOIN dept b ON a.deptno=b.deptno
)
SELECT * FROM romance WHERE rn<=2
ORDER BY deptno
/*
empno ename job mgr hierdate sal comm deptno
----------- ---------- --------- ----------- ------------------------------------------------------ --------- --------- -----------
7902 foro analyst 7566 1981-01-23 00:00:00.000 3000.00 NULL 10
7782 clark manager 7839 1987-06-09 00:00:00.000 2450.00 NULL 10
7788 socctt analyst 7566 1987-04-19 00:00:00.000 3000.00 NULL 20
7566 jones manager 7839 1981-04-02 00:00:00.000 2975.00 NULL 20
7698 blake manager 7839 1981-05-01 00:00:00.000 2850.00 NULL 30
7499 allen salesman 7698 1981-02-20 00:00:00.000 1600.00 300.00 30(所影响的行数为 6 行)*/