order by子句在临时表中不起作用？ [英] order by clause is not working in temp table?

问题描述

我想在oracle的员工表中找到两个最高薪水的总和。

我的员工表格为

i want to Find sum of two maximum salary from employee table in oracle.
my employee table as

EMP_ID               EMP_NAME              CONTACTNO     SALARY
-------------------- -------------------- ---------- ----------
Emp01                Mohan Chandra        9560498289      16500 
Emp02                Dinesh               9856895689      21000 
Emp03                Sachin               7858968956      18000 
Emp04                Ajay                 9136564555       9500 
Emp05                Sneha                7585963212      10625 
Emp06                Rahul                9163568958      11300 
Emp07                Divya                7539895689       8500 
Emp08                Nisha                9758695645      11485 

现在我正在写查询

now i am writing query as

create table temp  AS SELECT salary  from EMPLOYEE
where rownum<3 order by salary desc;

select sum(salary) from TEMP;

但它显示结果37500而不是39000.

当我写查询查看临时表所以它显示

but it is showing result 37500 instead of 39000.
when i written query to see temp table so it shows

 SALARY
----------
     21000 
     16500 

而不是

instead of

 SALARY
----------
     21000 
     18000 

所以我应该为此做些什么。

我想从员工表中找到两个最高工资的总和。



我的另一个问题是我想选择empname，salary，bonus.and bonus栏将是每个员工三个月的工资。那我该怎么办？请给我解决方案。

so what should i do for this.
i want to find sum of two maximum salary from employee table.

my another question is i want to select empname,salary, bonus.and bonus column will be three months of salary per employees.so what should i do?please give me solution.

    EMP_NAME          Salary     Bonus
-------------- ----------            ----------
         Mohan Chandra     16500      49500
         Dinesh            21000      63000
   .
   .
   .
   .
   .
and so on

推荐答案

为什么要强行打开门？



甲骨文很清楚 RANK功能 [ ^ ]，它提供了获得第k个工资值的方法：

Why to force doors wide open?

Oracle well know RANK function[^], which provides a way to get k-th value of salary:

select SUM(salary) sumof2salaries from (
  select e.*, row_number() over (order by salary desc) rn
  from emp e
)
where rn in (1, 2);

Oth资源：

排名 [ ^ ]

IN Condition [ ^ ]

sql - 如何选择带有数据库Oracle的第二高薪？ - 堆栈溢出 [ ^ ]





如果你想要为每个用户获得3个月的工资+奖金，你必须以这种方式改变你的查询：

Other resources:
RANK[^]
IN Condition[^]
sql - How can I select the record with the 2nd highest salary in database Oracle? - Stack Overflow[^]


If you would like to get salary + bonus for each user for 3 months, you have to change your query this way:

select empname, SUM(e.salary + e.bonus) totalSalary
from emp e
where e.paydate between to_date ('2015/10/01', 'yyyy/mm/dd') and to_date ('2015/12/31', 'yyyy/mm/dd')
group by empname

BETWEEN条件 [ ^ ]

Oracle/PLSQL:BETWEEN条件 [ ^ ]

这篇关于order by子句在临时表中不起作用？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！