我有一个查询(在下面显示),生成以下结果集(此操作将继续处理53,000条记录)
+--------+---------+--------+--------+------------+------------+
| emp_no | counter | emp_no | salary | from_date | to_date |
+--------+---------+--------+--------+------------+------------+
| 10001 | 1 | 10001 | 60117 | 1986-06-26 | 1987-06-26 |
| 10001 | 1 | 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| 10001 | 1 | 10001 | 66074 | 1988-06-25 | 1989-06-25 |
| 10001 | 1 | 10001 | 84917 | 1999-06-23 | 2000-06-22 |
| 10001 | 1 | 10001 | 85112 | 2000-06-22 | 2001-06-22 |
| 10001 | 1 | 10001 | 85097 | 2001-06-22 | 2002-06-22 |
| 10001 | 1 | 10001 | 88958 | 2002-06-22 | 9999-01-01 |
| 10002 | 2 | 10002 | 65828 | 1996-08-03 | 1997-08-03 |
| 10002 | 2 | 10002 | 65909 | 1997-08-03 | 1998-08-03 |
| 10002 | 2 | 10002 | 67534 | 1998-08-03 | 1999-08-03 |
| 10002 | 2 | 10002 | 69366 | 1999-08-03 | 2000-08-02 |
| 10002 | 2 | 10002 | 71963 | 2000-08-02 | 2001-08-02 |
| 10002 | 2 | 10002 | 72527 | 2001-08-02 | 9999-01-01 |
...
在X号员工可以存储多个薪水的地方,我用来获取此薪水的查询是:
SELECT * FROM (
SELECT dept_emp.emp_no, @counter := @counter+1 AS counter
FROM (select @counter:=0) AS initvar, dept_emp)
AS employeeFilter
LEFT JOIN(salaries)
ON (salaries.emp_no = employeeFilter.emp_no)
WHERE counter <= (25/100 * @counter) LIMIT 100;
我希望为每个唯一的emp_no(最近的薪水行)(基于to_date
字段)获取一行。
+--------+---------+--------+--------+------------+------------+
| emp_no | counter | emp_no | salary | from_date | to_date |
+--------+---------+--------+--------+------------+------------+
| 10001 | 1 | 10001 | 88958 | 2002-06-22 | 9999-01-01 |
| 10002 | 2 | 10002 | 72527 | 2001-08-02 | 9999-01-01 |
该@counter
实施是不是这个问题很重要,但它确实需要保持在该查询,因为我期待最终建立一个查询,将产生的切割25%部门的成本和@counter
可变让我限制结果的百分比。
试图使用DISTINCT,select DISTINCT (emp_no)
但我并不是最好的人,这只会引起更多问题。
您可以使用JOIN做出与ROW_NUMBER相同的逻辑:
SELECT * FROM (
SELECT t.*,count(*) as rnk
FROM (YourQuery/table) t
INNER JOIN (YourQuery/table) s
ON (t.emp_no = s.emp_no AND t.from_date <= s.from_date))
WHERE rnk = 1
或与相关查询:
SELECT * FROM (YourQuery) t
WHERE from_date = (select max(s.from_date)
FROM (YourQuery) s
WHERE s.emp_no = t.emp_no)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句