This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / have a employee table like this, find the 5th highest salary in it
-interview(intervieweree);
2002-2-15
{572}
(#370429@0)
-
如果是SQL 2000, Select top 5 * from employee
如果是Oracle，我去查一下先，hehe
-hugefox(hugefox);
2002-2-16
(#371376@0)
-
不好意思，把上面的查询当成子查询，查最少的那个就可以了
-hugefox(hugefox);
2002-2-16
(#371388@0)
-
oracleselect name from tablename where rownum = 5 order by salary
-truemimi(咪咪流浪记);
2002-2-17
{59}
(#371641@0)
-
如果有两条结果，你的语句能查出来吗？
-guoyifan(ooo);
2002-2-18
(#372426@0)
-
Oracle: select distinct name from tablename where salary = (select sum(salary)/count(*) as x from tablename where rownum = 5 group by x order by x);
-guoyifan(ooo);
2002-2-18
(#372435@0)
-
SQL Server:select * from employee where salary= (
select top 1 salary from employee
where salary not in (select distinct top 4 salary from employee order by salary )
order by salary )
-yangn(Raymond);
2002-2-18
{178}
(#372459@0)
-
almost correctthe answer is
select * from employee where salary=
(
select max(salary) from employee
where salary not in
(
select top 4 salary from employee order by salary desc)
)
)
-interview(intervieweree);
2002-2-18
{182}
(#372542@0)
-
Yes. I was too careless and missed "desc" in my statement, which returned the 5th lowest salary instead.:)
-yangn(Raymond);
2002-2-18
(#372591@0)
-
思路基本就是这样：先找出5个最高的值，然后取他们中最低的，就是第五高的。之后按这个工资值找出所有的符合的人。应该有多少都查得出来的。
-hugefox(hugefox);
2002-2-19
(#373089@0)
-
你们公司的人怎么挣这么少？不是说IT的都能挣个十万八万的吗？
-rollor(Rollor);
2002-2-18
(#372457@0)
-
看明白了再说话省得别人笑话
-guoyifan(ooo);
2002-2-18
(#372468@0)
-
压根就没往明白看。但我想说就说，怕什么人家笑话？
-rollor(Rollor);
2002-2-18
(#372472@0)
-
无聊者有理
-guoyifan(ooo);
2002-2-18
(#372481@0)
-
你有理。
-rollor(Rollor);
2002-2-18
(#372484@0)
-
不好意思，忘了往后面加个0，哈哈。。。。
-interview(intervieweree);
2002-2-18
(#372535@0)