注册做网站的营业执照,大红门桥做网站,美容院网站建设,大连培训网站建设1、描述
有一个员工表dept_emp简况如下#xff1a; 有一个薪水表salaries简况如下#xff1a; 获取每个部门中当前员工薪水最高的相关信息#xff0c;给出dept_no, emp_no以及其对应的salary#xff0c;按照部门编号dept_no升序排列#xff0c;以上例子输出如下: 2、题目…1、描述
有一个员工表dept_emp简况如下 有一个薪水表salaries简况如下 获取每个部门中当前员工薪水最高的相关信息给出dept_no, emp_no以及其对应的salary按照部门编号dept_no升序排列以上例子输出如下: 2、题目建表
drop table if exists dept_emp ;
drop table if exists salaries ;
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE salaries (
emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
INSERT INTO dept_emp VALUES(10001,d001,1986-06-26,9999-01-01);
INSERT INTO dept_emp VALUES(10002,d001,1996-08-03,9999-01-01);
INSERT INTO dept_emp VALUES(10003,d002,1996-08-03,9999-01-01);INSERT INTO salaries VALUES(10001,88958,2002-06-22,9999-01-01);
INSERT INTO salaries VALUES(10002,72527,2001-08-02,9999-01-01);
INSERT INTO salaries VALUES(10003,92527,2001-08-02,9999-01-01);3、答案
3.1 每个部门最高的薪水
select d.dept_no,max(s.salary) salary
from dept_emp d,salaries s
where d.emp_nos.emp_no
group by d.dept_no;3.2 (将员工、部门、薪水整合在一张表内)
select d.emp_no,d.dept_no,s.salary
from dept_emp d,salaries s
where d.emp_nos.emp_no;3.3(将t1和t2整合)
select t1.dept_no,t2.emp_no,t1.salary
from (表t1) t1
join (表t2) t2
on t1.dept_not2.dept_no and t1.salaryt2.salary
order by t1.dept_no;select t1.dept_no,t2.emp_no,t1.salary
from (select d.dept_no,max(s.salary) salary from dept_emp d,salaries swhere d.emp_nos.emp_nogroup by d.dept_no) t1
join (select d.emp_no,d.dept_no,s.salary from dept_emp d,salaries swhere d.emp_nos.emp_no) t2
on t1.dept_no t2.dept_no and t1.salaryt2.salary
order by t1.dept_no;
