如何在SQL中找到第n个最高薪水 [英] How to find nth Highest salary in SQL

问题描述

大家好，





从emp e1中选择ctc，其中（N-1）=（select count（distinct ctc）from emp其中e1.CTC< ctc）>





以上查询是表格中第N个最高薪水。



我不清楚该查询背后的逻辑。任何人都可以详细解释上面的查询。提前谢谢。

解决方案

< blockquote>它的工作原理 - 很糟糕 - 通过查看每行的ctc值并将其与大于它的值的数量进行比较。那么它对每一行的作用是检查内部选择中的整个表并比较每个值...讨厌，缓慢和有些愚蠢。



一个更好的解决方案是按工资订购数据，然后得到第五项：

  WITH  myTableWithRows  AS （
  SELECT （ROW_NUMBER（） OVER （ ORDER   BY  emp.ctc）） as  row，* 
  FROM  emp）
  SELECT  *  FROM  myTableWithRows  WHERE  row =  5  

您可能希望在其中添加GROUP BY子句以限制工资仅限于不同的值。

  SELECT  *  / *  这是外部查询部分* /  
  FROM 员工Emp1 
  WHERE （N-1）=（< span class =code-comment> / *  子查询从这里开始* /  
  SELECT  COUNT（ DISTINCT （Emp2.Salary））
  FROM 员工Emp2 
  WHERE  Emp2.Salary> Emp1.Salary）

这是一个核心子查询

试试这个结果集会让你更好地理解< br $> b $ b

  SELECT  E1.Salary，
（ / *  核心子查询* /  
  SELECT  COUNT（ DISTINCT （E2.Salary））
  FROM 员工E2 
  WHERE  E2.Salary> E1.Salary 
） as  salcount1，
（ / *  核心子查询* /  
  SELECT  COUNT（ DISTINCT （E2.Salary））
  FROM 员工E2 
 < span class =code-keyword> WHERE  E2.Salary< E1.S alary 
） as  salcount2 
  FROM 员工E1 

Hi all,


Select ctc from emp e1 where (N-1)=(select count(distinct ctc) from emp where e1.CTC <ctc)>


The above query is for Nth highest salary in table.

I am not clear of logic behind that query.Can any one explain above query in detail.Thanks in advance.

解决方案

it works - badly - by looking at the ctc value fro each row and comparing it with the number of values which are greater than it. So what it does for each row is examine the entire table in the inner select and compare each value...nasty, slow and somewhat stupid.

A better solution would be to order the data by salary and then just get the fifth item:

WITH myTableWithRows AS (
    SELECT (ROW_NUMBER() OVER (ORDER BY emp.ctc)) as row,*
    FROM emp)
SELECT * FROM myTableWithRows WHERE row = 5

You may want to add a GROUP BY clause in there to restrict the salaries just just the distinct values.

SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

well it is a corelated sub query
just try this the result set will give you a better understand

SELECT  E1.Salary,
    ( /* Corelated Subquery  */
    SELECT COUNT(DISTINCT(E2.Salary))
    FROM Employee E2
    WHERE E2.Salary > E1.Salary
    )as salcount1,
    ( /* Corelated Subquery  */
    SELECT COUNT(DISTINCT(E2.Salary))
    FROM Employee E2
    WHERE E2.Salary < E1.Salary
    ) as salcount2
FROM Employee E1

这篇关于如何在SQL中找到第n个最高薪水的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！