Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

database - SQL query to find Nth highest salary

I am referring to following query to find Nth highest salary of a employee.

select sal from emp t where &n = (select count(sal) from (select distinct sal 
 from emp) where t.sal<=sal);

One gentleman said this query works. Could someone please explain how equating a COUNT ( which really will be value between 1 to X where X is total distinct salaries) to &n produce this result ?

I am trying to understand how database handles this query internally and produces result ?

Thank you.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

First, the query will return the nth lowest salary value. To return the nth highest salary value you must change t.sal <= sal to t.sal >= sal.

Next, this query works by first finding the distinct list of salary values as one derived table and then determines the number of employees that have a salary less than each one in this list. t.sal <= sal is taking the derived table (which most databases would require have an alias) and comparing each value against the outer emp table. It should be noted that this will return multiple rows in the case of a tie.

To manually trace the output, we need some inputs:

Alice       | 200
Bob         | 100
Charlie     | 200
Danielle    | 150

Select Distinct sal
From emp

Gives us

200
100
150

Now we analyze each row in the outer table

Alice - There are 3 distinct salary values less than or equal to 200
Bob - 1 rows <= 100
Charlie - 3 rows <= 200
Danielle - 2 row <= 150

Thus, for each salary value we get the following counts (and reordered by count):

Bob 1
Danielle 2
Charlie 3
Alice 3

The most important aspect that I think you are overlooking is that the outer emp table is correlated to the inner count calculation (which is why it is called a correlated subquery). I.e., for each row in the outer emp table, a new count is calculated for that row's salary via t.sal <= sal. Again, most database systems would require the inner most query to have an alias like so (note the As Z alias):

Select sal
From emp As t
Where &n =  (
            Select Count(Z.sal)
            From    (
                    Select Distinct sal
                    From emp
                    ) As Z
            Where t.sal <= Z.sal
            )

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...