34,590
社区成员
发帖
与我相关
我的任务
分享
SELECT
E.`ENAME`,
E.`SAL`,
FROM
EMP E,
SALGRADE S
WHERE
(SELECT GRADE FROM SALGRADE WHERE (E.DEPTNO = 30 AND E.`SAL` >= S.`LOSAL` AND E.`SAL` <=`DEPT` S.`HISAL`)) IN
(SELECT GRADE FROM SALGRADE WHERE (E.`DEPTNO`!=30 AND E.`SAL` >= S.`LOSAL` AND E.`SAL` <= S.`HISAL`));
##部门表
#DROP IF EXISTS TABLE DEPT;
CREATE TABLE DEPT(
DEPTNO int PRIMARY KEY,##部门编号
DNAME VARCHAR(14) , ##部门名称
LOC VARCHAR(13) ##部门地址
) ;
INSERT 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');
##员工表
#DROP IF EXISTS TABLE EMP;
CREATE TABLE EMP(
EMPNO int PRIMARY KEY, #员工编号
ENAME VARCHAR(10), #员工姓名
JOB VARCHAR(9), #员工工作
MGR int, #员工直属领导编号
HIREDATE DATE, #入职时间
SAL double, #工资
COMM double, #奖金
DEPTNO int #对应dept表的外键
);
## 添加 部门 和 员工 之间的主外键关系
ALTER TABLE EMP ADD CONSTRAINT FOREIGN KEY EMP(DEPTNO) REFERENCES DEPT (DEPTNO);
INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);
#工资等级表
#DROP IF EXISTS TABLE SALGRADE;
CREATE TABLE SALGRADE(
GRADE int, #等级
LOSAL double, #最低工资
HISAL double ); #最高工资
INSERT 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);
if object_id('tempdb..#dept') is not null
drop table #dept
go
create table #dept(
deptno int primary key,
dname varchar(14),
loc varchar(13)
)
insert into #dept(deptno,dname,loc)
select 10,'ACCOUNTING','NEW YORK' union all
select 20,'RESEARCH','DALLAS' union all
select 30,'SALES','CHICAGO' union all
select 40,'OPERATIONS','BOSTON'
go
-- select * from #dept
go
if object_id('tempdb..#emp') is not null
drop table #emp
go
create table #emp(
empno int primary key, --员工编号
ename varchar(10), --员工姓名
job varchar(9),
mgr int,
hiredate date,
sal decimal(10,2), --工资
comm decimal(10,2),
deptno int
)
insert into #emp(empno,ename,[job],mgr,hiredate,sal,comm,deptno)
select 7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20 union all
select 7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30 union all
select 7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30 union all
select 7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20 union all
select 7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30 union all
select 7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30 union all
select 7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10 union all
select 7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20 union all
select 7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10 union all
select 7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30 union all
select 7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20 union all
select 7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30 union all
select 7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20 union all
select 7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10
go
-- select * from #emp
go
if object_id('tempdb..#salgrade') is not null
drop table #salgrade
go
create table #salgrade(
grad int identity(1,1) not null,
losal decimal(10,2),
hisal decimal(10,2)
)
insert into #salgrade(losal,hisal)
select 700,1200 union all
select 1201,1400 union all
select 1401,2000 union all
select 2001,3000 union all
select 3001,9999
go
-- select * from #salgrade
--先计算所有员工的工资在哪个级别:#salgrade.grad
with cta as (
select a.empno
,a.ename
,a.sal
,a.deptno
,[Level] = (select x.grad from #salgrade x where a.sal between x.losal and x.hisal)
from #emp a
where a.deptno =30
)
--只查询重复的记录数据
select * from cta where [Level] in (select [Level] from cta group by cta.[Level] having count(1) >=2)
执行结果:
SELECT *
FROM EMP A
JOIN
(SELECT DISTINCT C.LOSAL,C.HISAL
FROM EMP B
JOIN SALGRADE C ON B.SAL BETWEEN C.LOSAL AND C.HISAL
WHERE DEPTNO=30) AS D ON A.SAL BETWEEN D.LOSAL AND D.HISAL
WHERE DEPTNO<>30