sql 错题 group by 使用问题

题目描述:

有一个员工表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')

最终答疑:

语法：

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 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

reference:

题目来源于牛客

https://www.nowcoder.com/practice/4a052e3e1df5435880d4353eb18a91c6?tpId=82&tags=&title=&difficulty=0&judgeStatus=0&rp=1

A表中的AID是主码，B表中的AID是外码，参照A表的主码，含义就是在B表中的AID不能随便取值，只能取A表中出现过的值，前台处理可以用一个下拉框来实现，下拉框中的数据从A表中获取，然后让用户从下拉框中选择数据，就不会出现所选的数据不在A表中出现的情况。

---