取分组后每组最大值对应记录再与外键关联
【问题】
SQL select query using joins, group by and aggregate functions
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)
FROMsalary_increase AS i
RIGHTJOINemp_table AS e
ON i.emp_id=e.emp_id
GROUPBY 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
FROMsalary_increase AS i
RIGHTJOINemp_table AS e
ON i.emp_id=e.emp_id
RIGHTJOIN
(
SELECT emp_id,inc_date FROM salary_increase
WHERE inc_amount=max(inc_amount)GROUPBY emp_id
)AS t
ON e.emp_id=t.emp_id
GROUPBY e.emp_id;
this gives an error ‘Invalid use of group function’. Does anyone know what i’m doing wrong?
别人的回答：
You can’t do thisWHERE inc_amount=max(inc_amount)in the where clause, either useHAVINGor do it in the condition of join, try this instead:
SELECT
e.emp_id,
e.inc_date,
t.TotalInc,
t.MaxIncAmount
FROMsalary_increase AS i
INNERJOINemp_table ASe ON i.emp_id=e.emp_id
INNERJOIN
(
SELECT
emp_id,
MAX(inc_amount)AS MaxIncAmount,
COUNT(i.inc_amount)AS TotalInc
FROM salary_increase
GROUPBY emp_id
)ASt ON e.emp_id = t.emp_id AND e.inc_amount = t.MaxIncAmount;
【回答】
这个问题的难点是 max 函数只能计算出最大值，但不能取出“最大值对应的记录”，再加上分组和关联，就会让问题更显复杂。其实你也可以用 keep 函数来解决，或者用 top 加 row_number，或者用窗口函数等方法，当然，这些方法都比较麻烦。
如果数量不是非常大，这种情况建议采用 SPL。SPL 中的 top 函数可以取出最大值对应的记录，比如 salary.top(amount;3) 取出 amount 最小的三条记录，salary.top(-amount;1) 取出 amount 最大的那条记录。下面的代码可以解决你的问题：
A |
|
1 |
= db.query("select * from salary_increase") |
2 |
=A1.group(emp_id) |
3 |
=A2.(~.top(-1;inc_amount)) |
4 |
=A3.union() |
5 |
=db.query("select * from emp_table") |
6 |
=join(A4,emp_id; A5,emp_id) |
A1：通过 SQL 取 salary_increase 表中的数据
A2：根据 emp_id 分组
A3：取出每组中 inc_amount 最大值对应的记录
A4：将各组取出的记录合并
A5：通过 SQL 取 emp_table 表中的数据
A6：通过 emp_id 关联
集算器是结构化计算开发工具，和 JAVA 或报表工具很容易集成，可以参考【Java 如何调用 SPL 脚本】
