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?
这会给出错误“组功能的无效使用”。有谁知道我做错了什么?
1 个解决方案
#1
5
You can't do this WHERE inc_amount=max(inc_amount)
in the where clause, either use HAVING
or do it in the condition of join, try this instead:
你不能在where子句中执行WHERE inc_amount = max(inc_amount),要么使用HAVING要么在连接条件下执行,请尝试这样做:
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;
#1
5
You can't do this WHERE inc_amount=max(inc_amount)
in the where clause, either use HAVING
or do it in the condition of join, try this instead:
你不能在where子句中执行WHERE inc_amount = max(inc_amount),要么使用HAVING要么在连接条件下执行,请尝试这样做:
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;