题目描述:
有一个员工表dept_emp简况如下:
有一个薪水表salaries简况如下:
获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列，以上例子输出如下:
（注意: Mysql与Sqlite select 非聚合列的结果可能不一样)
我的答案:
select de.dept_no,s.emp_no,max(s.salary) from dept_emp de
inner join salaries s on de.emp_no = s.emp_no
group by de.dept_no order by de.dept_no ASC
错误提示:
测试数据:
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,'d004','1995-12-03','9999-01-01');
INSERT INTO dept_emp VALUES(10004,'d004','1986-12-01','9999-01-01');
INSERT INTO dept_emp VALUES(10005,'d003','1989-09-12','9999-01-01');
INSERT INTO dept_emp VALUES(10006,'d002','1990-08-05','9999-01-01');
INSERT INTO dept_emp VALUES(10007,'d005','1989-02-10','9999-01-01');
INSERT INTO dept_emp VALUES(10009,'d006','1985-02-18','9999-01-01');
INSERT INTO dept_emp VALUES(10010,'d006','2000-06-26','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,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
INSERT INTO salaries VALUES(10009,95409,'2002-02-14','9999-01-01');
INSERT INTO salaries VALUES(10010,94409,'2001-11-23','9999-01-01');
最终答疑:
错误点1：d.emp_no是非聚合字段，不能出现在SELECT。因为一个聚合字段(dept_no)对应多个非聚合字段(emp_no)，所以选择的时候，会随机选择非聚合字段中的任何一个，于是出错。
语法：
SELECT column_1, column_2, … column_n, aggregate_function(expression), constant
FROM tables
WHERE predicates
GROUP BY column_1, column_2, … column_n
HAVING condition_1 … condition_n;
注意：因为聚合函数通过作用一组值而只返回一个单一值，因此，在SELECT语句中出现的字段要么为一个聚合函数的输入值，如COUNT(course)，要么为GROUP BY语句中指定的字段，要么是常数，否则会出错。
注意：因为聚合函数通过作用一组值而只返回一个单一值，因此，在SELECT语句中出现的字段要么为一个聚合函数的输入值，如COUNT(course)，要么为<u>GROUP BY语句中指定的字段</u>，要么是<u>常数</u>，否则会出错。
简而言之：使用GROUP BY子句时，SELECT子句中只能有<u>聚合键、聚合函数、常数</u>。
解法一:
SELECT d1.dept_no, d1.emp_no, s1.salary
FROM dept_emp as d1
INNER JOIN salaries as s1
ON d1.emp_no=s1.emp_no
WHERE s1.salary in (SELECT MAX(s2.salary)
FROM dept_emp as d2
INNER JOIN salaries as s2
ON d2.emp_no=s2.emp_no
AND d2.dept_no = d1.dept_no
) ORDER BY d1.dept_no ASC
解法二(with as):
WITH t1 AS (SELECT de.dept_no,s.emp_no,s.salary
FROM dept_emp de INNER JOIN salaries s
ON de.emp_no = s.emp_no),
t2 AS (SELECT dept_no,max(salary) AS m_salary
FROM t1 GROUP BY dept_no)
SELECT t1.dept_no,t1.emp_no,t1.salary
FROM t1 JOIN t2 ON
t1.dept_no = t2.dept_no AND t1.salary = t2.m_salary
ORDER BY t1.dept_no
基本用法是这样：with t1 as (select xxx from x), t2 as (select yyy from y)
select t1.a, t2.b from t1 join t2 on t1.a = t2.b
1. 构造大宽表 2.求max salary 3. 俩表 join，得出结果
reference:
题目来源于牛客
https://www.nowcoder.com/practice/4a052e3e1df5435880d4353eb18a91c6?tpId=82&tags=&title=&difficulty=0&judgeStatus=0&rp=1
