Question

我指的是以下询问:找到一名雇员最高工资。

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

一位gent子说,这种争.是行之有效的。能否解释一下,如何将COUNT(如果X是完全不同的工资)与Ap;n 得出这一结果?

我正在设法了解数据库如何在内部处理这一难题并产生结果?

谢谢。

Answer 1

首先,询问将回到<代码>n>lowest薪金价值。回到<代码>nth,最高薪金价值为:t.sal <= sal to t.sal >= sal。

其次,这一询问首先将不同的工资价值清单作为一份衍生表格,然后确定工资低于本清单中每名雇员的人数。 <代码>t.sal <= sal正在采用衍生表格(大多数数据库需要一个别处),并将每个数值与外部<代码>>><>>>表进行比较。应当指出的是,如果存在类似情况,这将退回多个方面。

为了人工追踪产出,我们需要一些投入:

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

Select Distinct sal
From emp

我们

200
100
150

我们现在分析各行各行在外表

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

因此,就每个薪金价值而言,我们得出以下各点:

Bob 1
Danielle 2
Charlie 3
Alice 3

我认为你眼下最重要的方面是:外部<代码><>>。表中为:correlated,以内计计算(因此,它被称为“correlated subquery”。页: 1 表格通过<代码>t.sal <= sal计算了该行的薪金。同样,大多数数据库系统将要求最内部的系统有类似内容(注:<代码>As Zs):

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
            )

Answer 2

select sal 
from (
  select sal, 
         dense_rank() over (order by sal desc) as rnk
) t
where rnk = 5;

用你想要的“nth”代替。

Answer 3

取得最高薪金价值就只能使N值上升。

Select Min(Salary) From (Select Top N * From Table_Name Order by Salary Desc);

Answer 4

SELECT TOP 1 salary
FROM (
SELECT DISTINCT TOP n salary
FROM employee
ORDER BY salary DESC) a
ORDER BY salary
where n > 1 (n is always

Answer 5

  SELECT Max(Salary) as Salary
    FROM employee
    where  Salary Not in 
    (SELECT TOP N Salary FROM employee ORDER BY Salary DESC)
  where N is defined by you.

So let s say you have the following salaries in the table employee: Here employeeID and Salary are the columns of employee table.

EmployeeID Salary

 101  25,000
 154  89,000
 987  42,000
 450  12,000
 954  50,000

如果我们想要看到第四高薪。

Salary

25,000

薪酬收入最高。

Answer 6

在数据库中记录数据条目如

employ_id    NAME     salary
101          Henry    24000
102          Smith    24000
105          Roy      17000  
106          Robbin   15000 
702          Mac      2500
708          Bill     2100
709          Kane     2000
710          Ted      2000

在这里,有些雇员的工资相同,因此如何计算工资(最高/最低)

第3类最高薪金的计算

select * from emloyees where salary in (select salary from (select rownum rank , salary from (select distinct salary from employees order by salary **desc**)) where rank =3;

ans = 15000

similarly to calculate 3rd lowest salary Type same Query with a small change instead of desc type asc

select * from emloyees where salary in (select salary from (select rownum rank , salary from (select distinct salary from employees order by salary **asc**)) where rank =3;

希望这将有助于你的工作。

Answer 7

改变最高薪金价值就只能使N值贬值。

SELECT e1.EmployeeName, e1.EmployeeSalary from Employee e1
where N = (
select COUNT(e2.EmployeeSalary) from Employee e2 where e2.EmployeeSalary >= e1.EmployeeSalary)

Answer 8

实现这一目的有许多途径:

1 P-4, 1 P-3, 1 FS, 1 NS

 Select Top(1 P-4, 1 P-3, 1 FS, 1 NSsal from emp 
    where sal not in (select DISTINCT top(n-1 P-4, 1 P-3, 1 FS, 1 NSsal from emp order by sal desc)

2)

select salary     
          from (
           select salary,
           roe_number() over (order by salary ) as row from emp
          ) emp1
  where row= n;

This query will not work if multiple rows have the same values one after another.

3)

select salary     
              from (
               select salary,
               dense_rank() over (order by salary ) as row from emp
              ) emp1
      where row= n;

This will create a unique row number for all unique salary amounts.

4)

 Select Min(sal) From 
       (Select DISTINCT Top n * From emp Order by sal Desc)as emp1;

5)

   SELECT * FROM emp Emp1
            WHERE (n-1 P-4, 1 P-3, 1 FS, 1 NS= (
                             SELECT COUNT(DISTINCT(Emp2.Sal))
                             FROM emp Emp2
                             WHERE Emp2.Sal > Emp1.Sal)

Answer 9

select salary 
from (Select ROW_NUMBER() over(order by salary desc) as row ,salary from Employee)as temp
where row=2; 
---2nd highest salary

Answer 10

n donated as nth number you want. like : i want second highest so my query will be n=2

SELECT salary FROM Employee ORDER BY Salary DESC LIMIT 1,1

页: 1

SELECT salary FROM Employee ORDER BY Salary DESC LIMIT n-1,1

Answer 11

学历:

select 
    ename  
    ,sal  
    ,dense_rank() over (order by sal desc) ranking  
from   emp;

产出:

ENAME   SAL   RANKING
KING    5000    1   
FORD    3000    2  
SCOTT   3000    2  
JONES   2975    3  
CLARK   2850    4  
BLAKE   2850    4  
ALLEN   1600    5

rap filter filter filter filter filter salary

学历:

select *  
from  
(  
  select ename  
        ,sal  
        ,dense_rank() over (order by sal desc) ranking  
  from   emp  
)  
where ranking = 4 -- Replace 4 with any value of N

产出:

ENAME  SAL  RANKING
BLAKE  2850     4  
CLARK  2850     4

EmployeeID Salary

Salary

友情链接