我有三個表
表一 DEPT
DEPTNO DNAME LOC
------ ---------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
表二 EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ------ --------- ------ ---------- ------ ------ --------
7369 SMITH CLERK 7902 1980-12-17 800 (NULL) 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 2975 (NULL) 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 2850 (NULL) 30
7782 CLARK MANAGER 7839 1981-06-09 2450 (NULL) 10
7788 SCOTT ANALYST 7566 1987-07-03 3000 (NULL) 20
7839 KING PRESIDENT (NULL) 1981-11-17 5000 (NULL) 10
7844 TURNER SALESMAN 7698 1981-09-08 1500 0 30
7876 ADAMS CLERK 7788 1987-07-13 1100 (NULL) 20
7900 JAMES CLERK 7698 1981-12-03 950 (NULL) 30
7902 FORD ANALYST 7566 1981-12-03 3000 (NULL) 20
7934 MILLER CLERK 7782 1981-01-23 1300 (NULL) 10
表三 SALGRADE
GRADE LOSAL HISAL
------ ------ --------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
現在問題是 想要回傳與30部門員工工資等級相同的員工姓名與工資。 應該如何實作呢
以下是我寫的 ,但是查詢后沒有結果
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);
uj5u.com熱心網友回復：
demo：
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)
執行結果：
uj5u.com熱心網友回復：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/282981.html
標籤：基礎類
下一篇：lun腳本怎么解密