查找前几个或最多观察值时使用TOP或Rank [英] Use TOP or Rank when finding the first few or the most observations

问题描述

我已经搜索了其他人的代码，并尝试自定义
，但似乎当问题是最大时，它就可以了。
当要找到前100名时，它没有用。
我想在公司雇用头100名员工。
我首先尝试了TOP（100），然后尝试了RANK（），我想它们都可以工作。
我对语法不是很熟悉。
我想知道有人能对我的代码提供任何建议吗？

非常感谢！

  SELECT d.department_name，d.department_id，e.first_name，e.hire_date，e.salary 
来自Dtable_department d加入
 Etable_employee e 
在e.department_id = d.department_id 
上，其中hire_date =（选择DENSE_RANK（）OVER（PARTITION BY e.hire_date ORDER BYhire_date DESC）AS E_RANK_hire WHERE rownum = 100））
 GROUP BY E_RANK_hire 
由E_RANK_hire订购
 
  

解决方案

要获得公司雇用的前100名员工

首先，请注意以下两个查询的结果中均包含平局。例如即使您的雇员具有相等的雇用日期，也将其包括在列表中，这意味着列表中至少有100人。

如果您的数据库版本为 12c-，那么您需要使用子查询来返回 dense_rank（）函数的结果：

 从
中选择部门名称，部门ID，名字，雇用日期，薪水
（
选择d.department_name，d.department_id ，e.first_name，e.hire_date，e.salary，
 density_rank（）over（byhire_date排序）作为e_rank_hire 
从Dtable_department d 
加入Etable_employee e 
 on e.department_id = d.department_id 
）
其中e_rank_hire< = 100 
由e_rank_hire订购； 
  

如果您的数据库版本为 12c + ，则您不必为了 fetch 子句而使用子查询：

 从Dtable_department d 
中选择d.department_name，d.department_id，e.first_name，e.hire_date，e.salary 
加入Etable_employee e 
上e.department_id = d.department_id 
的hire_date 
订单将获取前100行并列的领带； 
  

请注意您的情况，使用 partition by 子句是错误的，应在 dense_rank（）函数的表达式中删除，并且聘用日期的顺序不应降序，而应升序。

前10名员工的演示

I have searched others' code and tried to customize in my case, but it seemed when the question was the "max", it worked. When it comes to find the top 100, it was not working. I am trying to get the first 100 people hired in the firm. I first tried TOP(100) and then RANK(), I guess they would both work. I was not super familiar with the syntax though. I wonder would anyone could kindly provide me with any suggestion on my code?

Thank you very much!

SELECT d.department_name, d.department_id, e.first_name, e.hire_date, e.salary 
from Dtable_department d join
     Etable_employee e
     on e.department_id = d.department_id
where hire_date = (select DENSE_RANK() OVER (PARTITION BY e.hire_date ORDER BY hire_date DESC) AS E_RANK_hire  WHERE rownum=100))
Group BY E_RANK_hire
order by E_RANK_hire

解决方案

To get the first 100 people hired in the firm

First of all, Be careful about the tie cases are included within the results of both queries below. e.g. even if you have employee with equal hire date, they're included in the lists, meaning lists have at least 100 people.

If your Database version is 12c-, then you need to use a subquery in which to return the result of dense_rank() function :

select department_name, department_id, first_name, hire_date, salary
  from
  (
   select d.department_name, d.department_id, e.first_name, e.hire_date, e.salary,
          dense_rank() over ( order by hire_date ) as e_rank_hire
     from Dtable_department d 
     join Etable_employee e
       on e.department_id = d.department_id
  )
 where e_rank_hire <= 100 
 order by e_rank_hire;

If your Database version is 12c+, then you do not need to use a subquery by the sake of fetch clause :

select d.department_name, d.department_id, e.first_name, e.hire_date, e.salary
  from Dtable_department d 
  join Etable_employee e
    on e.department_id = d.department_id
order by hire_date
fetch first 100 rows with ties;

Pay attention for your case that using partition by clause is wrong and should be removed within the dense_rank() function's expression, and order of hire dates shouldn't be descending but ascending.

Demo for Top 10 Employee

这篇关于查找前几个或最多观察值时使用TOP或Rank的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！