oracle 如何从员工表中获取最高工资和计数

nwsw7zdq 于 2023-10-16 发布在 Oracle

关注(0)|答案(4)|浏览(154)

我有一个任务，我需要以这样一种方式过滤，需要打印max(salary * months)和max(salary * months)发生的次数。
我的数据：

employee_id , name , months , salary
12          , Ami  , 15     , 1230
14          , Bik  , 1      , 678
15          , Tom  , 10     , 500
16          , Bob  , 12     , 3456
17          , Wil  , 1      , 9000
18          , Tim  , 14     , 1239
22          , Jil  , 15     , 1230

注：表中只有4列-> employee_id, name, months, salary

我写了一个查询，在这里我可以得到max(salary * months)
问题1：

select 
    max(msal) 
from 
    (select 
         employee_id, 
         name, 
         salary, 
         months, 
         (salary * months) as msal 
     from 
         employee);

问题2：

select 
    e1.employee_id, 
    e1.months, 
    e1.salary, 
    e2.employee_id, 
    (e2.salary * e2.months) as earnings 
from 
    employee e1 
join 
    employee e2 on e1.employee_id = e2.employee_id;

问题3：

select 
  emp,earnings 
from 
  (
    select 
      e1.employee_id as emp, 
      e1.months, 
      e1.salary, 
      e2.employee_id, 
      (e2.salary * e2.months) as earnings 
    from 
      employee e1 
      join employee e2 on e1.employee_id = e2.employee_id
  ) 
order by 
  earnings desc;

我在纸上计算了这些细节：

15  * 1230   ->  18450
1   * 678    ->  678
10  * 500    ->  5000
12  * 3456   ->  41472
1   * 9000   ->  9000
14  * 1239   ->  17346
15  * 1230   ->  18450

根据我的分析，我的输出应该是：41472 1

41472-> max(salary * months)
1     -> occurred twice

oracle

来源：https://stackoverflow.com/questions/73267847/how-to-get-max-salary-and-count-from-employee-table

4条答案

按热度按时间

mqkwyuun1#

你想看看工资 * 月的结果，计算他们的出现，只显示顶部的数字。所以，按工资 * 月分组，计数并取最上面一行：

select salary * month, count(*)
from employee
group by salary * month
order by salary * month desc
fetch first row only;

赞(0）回复(0）举报 2023-10-16

shstlldc2#

这里有一个选择：
样本数据：

SQL> with emp (employee_id, name, months, salary) as
  2    (select 12, 'ami', 15, 1230 from dual union all
  3     select 14, 'bik', 1 ,  678 from dual union all
  4     select 15, 'tom', 10,  500 from dual union all
  5     select 16, 'bob', 12, 3456 from dual union all
  6     select 17, 'wil', 1 , 9000 from dual union all
  7     select 18, 'tim', 14, 1239 from dual union all
  8     select 22, 'jil', 15, 1230 from dual
  9    ),

查询从这里开始：按降序排列值...

10  temp as
 11    (select salary * months max_value,
 12            count(*) cnt,
 13            rank() over (order by salary * months desc) rnk
 14     from emp
 15     group by salary * months
 16    )
 17  select max_value, cnt
 18  from temp
 19  where rnk = 1;           --> ... and, finally, fetch row(s) that rank as the highest

 MAX_VALUE        CNT
---------- ----------
     41472          1

SQL>

虽然你没有要求它，但是-这样的方法是好的，因为你可以很容易地得到-例如：

只需将where子句修改为

where rnk = 2

前3个值

where rnk <= 3

等

赞(0）回复(0）举报 2023-10-16

nuypyhwy3#

最后我可以写一个查询
在任何有效的方式编写查询。请提供您的解决方案

查询：

SELECT
    msal,
    COUNT(*)
FROM
    (
        SELECT
            months * salary AS msal
        FROM
            employee_salary
        WHERE
            ( months * salary ) IN (
                SELECT
                    MAX(months * salary)
                FROM
                    employee_salary
            )
    )
GROUP BY
    msal;

赞(0）回复(0）举报 2023-10-16

vu8f3i0k4#

SELECT MAX（salary * months）SELECT MAX（salary * months）FROM employee WHERE salary * months IN（SELECT MAX（salary * months）FROM employee）

赞(0）回复(0）举报 2023-10-16