MySQL 使用连接、分组依据和聚合函数的 SQL 选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20594953/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL select query using joins, group by and aggregate functions
提问by StickyCube
I have two tables with the following fields
我有两个包含以下字段的表
emp_table: emp_id, emp_name
salary_increase: emp_id, inc_date, inc_amount
I am required to write a query which gives the employee details, the number of times an employee has received a salary increase, the value of the maximum increase amount and the date of that increase. Here is what i have so far:
我需要编写一个查询，其中提供员工详细信息、员工获得加薪的次数、最大加薪金额和加薪日期。这是我到目前为止所拥有的：
SELECT e.*, count(i.inc_amount), max(i.inc_amount)
FROM salary_increase AS i
RIGHT JOIN emp_table AS e
ON i.emp_id=e.emp_id
GROUP BY e.emp_id;
this correctly gives all the requirements apart from the date on which the maximum increase was awarded. I have tried the following with no success:
这正确地给出了除了授予最大增加的日期之外的所有要求。我尝试了以下方法但没有成功：
SELECT e.*, count(i.inc_amount), max(inc_amount), t.inc_date
FROM salary_increase AS i
RIGHT JOIN emp_table AS e
ON i.emp_id=e.emp_id
RIGHT JOIN
(
SELECT emp_id, inc_date FROM salary_increase
WHERE inc_amount=max(inc_amount) GROUP BY emp_id
) AS t
ON e.emp_id=t.emp_id
GROUP BY e.emp_id;
this gives an error 'Invalid use of group function'. Does anyone know what i'm doing wrong?
这给出了一个错误“无效使用组功能”。有谁知道我做错了什么？
采纳答案by Mahmoud Gamal
You can't do this WHERE inc_amount=max(inc_amount)in the where clause, either use HAVINGor do it in the condition of join, try this instead:
你不能WHERE inc_amount=max(inc_amount)在 where 子句中HAVING这样做，要么在 join 条件下使用，要么在 join 条件下这样做，试试这个：
SELECT
e.emp_id,
e.inc_date,
t.TotalInc,
t.MaxIncAmount
FROM salary_increase AS i
INNER JOIN emp_table AS e ON i.emp_id=e.emp_id
INNER JOIN
(
SELECT
emp_id,
MAX(inc_amount) AS MaxIncAmount,
COUNT(i.inc_amount) AS TotalInc
FROM salary_increase
GROUP BY emp_id
) AS t ON e.emp_id = t.emp_id AND e.inc_amount = t.MaxIncAmount;
