如何获取表中的前10条记录和最后10条记录？
如何获取表中的前10条记录和最后10条记录？
提问于 2021-06-17 02:45:11
我需要找到10名员工，他们当前的工资和工资之间的差异最大，当他们被雇用的时候，以及10名差异最小的员工。该表类似于此salary table，包含2844047条记录。我的代码是：
WITH t1 AS (
SELECT emp_no, FIRST_VALUE(salary) OVER (PARTITION BY emp_no ORDER BY from_date) AS `first`
FROM salaries),
t2 AS (
SELECT emp_no, salary AS last
FROM salaries
WHERE to_date = '9999-01-01')
(SELECT DISTINCT emp_no, last - first AS `diff`
FROM t1
INNER JOIN t2 USING (emp_no)
ORDER BY `diff`
LIMIT 10)
UNION ALL
(SELECT DISTINCT emp_no, last - first AS `diff`
FROM t1
INNER JOIN t2 USING (emp_no)
ORDER BY `diff` DESC
LIMIT 10);但这需要很长时间才能执行。
to_date = '9999-01-01‘表示该员工仍在工作的条件。
如何优化此查询，使其执行速度更快？
回答 1
Stack Overflow用户
回答已采纳
发布于 2021-06-17 02:50:24
join不是必需的。也许这样会更快一点：
SELECT s.*
FROM (SELECT s.*,
ROW_NUMBER() OVER (ORDER BY salary - first ASC) as seqnum,
ROW_NUMBER() OVER (ORDER BY salary - first DESC) as seqnum_desc
FROM (SELECT s.*,
FIRST_VALUE(salary) OVER (PARTITION BY emp_no ORDER BY from_date) AS first
FROM salaries s
) s
WHERE to_date = '9999-01-01'
) s
WHERE seqnum_asc <= 10 or seqnum_desc <= 10;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68008432
复制相关文章
相似问题
腾讯云开发者
