(1) 取得每个部门最高薪水的人员名称:
(2)哪些人的薪水在部门平均薪水之上:
1.创建部门表:
DROP TABLE IF EXISTS `dept`;
CREATE TABLE `dept` (
`DEPTNO` int(2) NOT NULL COMMENT '部门编号',
`DNAME` varchar(14) DEFAULT NULL COMMENT '部门名称',
`LOC` varchar(13) DEFAULT NULL COMMENT '位置',
PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB;
插入数据:
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');
2:创建员工表
DROP TABLE IF EXISTS `emp`;
CREATE TABLE `emp` (
`EMPNO` int(4) NOT NULL COMMENT '员工编号',
`ENAME` varchar(10) DEFAULT NULL COMMENT '员工姓名',
`JOB` varchar(9) DEFAULT NULL COMMENT '工作岗位',
`MGR` int(4) DEFAULT NULL COMMENT '上级经理',
`HIREDATE` date DEFAULT NULL,
`SAL` double(7,2) DEFAULT NULL,
`COMM` double(7,2) DEFAULT NULL,
`DEPTNO` int(2) DEFAULT NULL,
PRIMARY KEY (`EMPNO`),
KEY `DEPTNO` (`DEPTNO`),
KEY `SAL` (`SAL`),
CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='员工编号';
插入数据:
INSERT INTO `emp` VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800.00', null, '20');
INSERT INTO `emp` VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600.00', '300.00', '30');
INSERT INTO `emp` VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250.00', '500.00', '30');
INSERT INTO `emp` VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975.00', null, '20');
INSERT INTO `emp` VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250.00', '1400.00', '30');
INSERT INTO `emp` VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850.00', null, '30');
INSERT INTO `emp` VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450.00', null, '10');
INSERT INTO `emp` VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7839', 'KING', 'PRESIDENT', null, '1981-11-17', '5000.00', null, '10');
INSERT INTO `emp` VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500.00', '0.00', '30');
INSERT INTO `emp` VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1981-05-23', '1100.00', null, '20');
INSERT INTO `emp` VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950.00', null, '30');
INSERT INTO `emp` VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000.00', null, '20');
INSERT INTO `emp` VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300.00', null, '10');
查看数据:
(1) 取得每个部门最高薪水的人员名称:
第1步:求出每个部门的最高薪水(根据部门编号进行分组求出最大值)
select e.DEPTNO,max(e.SAL) as maxSAL from emp as e group by e.DEPTNO;
第2步:将以上查询结果当为一个临时表t(DEPTNO,maxSAL),然后将t表和emp表进行连接,连接条件是DEPTNO,最后的where条件是:员工薪水等于部门最高薪水
select e.DEPTNO,e.ENAME,t.maxSAL,e.SAL from t inner join emp as e on t.DEPTNO=e.DEPTNO
where t.maxSAl=e.SAL;
将t替换为第一步的语句:
select e.DEPTNO,e.ENAME,t.maxSAL,e.SAL from
(select e.DEPTNO,max(e.SAL) as maxSAL from emp as e group by e.DEPTNO) as t
inner join emp as e on t.DEPTNO=e.DEPTNO
where t.maxSAl=e.SAL;
(2)哪些人的薪水在部门平均薪水之上:
第1步:先求出每个部门的平均薪水:
select e.DEPTNO,avg(e.SAL) as avgSAL from emp as e group by e.DEPTNO;
第2步:将以上结果作为临时表t(DEPTNO,avgSAL) ,然后与员工表进行连接,最后的where条件是员工得到薪水大于部门的平均薪水:
select t.DEPTNO,e.ENAME from t
inner join emp e on t.DEPTNO=e.DEPTNO
where e.SAL>t.avgSAL
然后将t替换为第1步中的语句:
select t.DEPTNO,e.ENAME from
(select e.DEPTNO,avg(e.SAL) as avgSAL from emp as e group by e.DEPTNO) as t
inner join emp e on t.DEPTNO=e.DEPTNO
where e.SAL>t.avgSAL;