급여 테이블에서 세 번째 또는 n 번째 최대 급여를 찾는 방법은 무엇입니까?

급여 테이블에서 세 번째 또는 n 번째 최대 급여를 찾는 방법은 무엇입니까?

---

최적화 된 방식으로 third or nth급여 table(EmpID,EmpName,EmpSalary)에서 최대 급여 를 찾는 방법은 무엇입니까?

---

사용 ROW_NUMBER하거나 (단일 원하는 경우) DENSE_RANK(관련된 모든 행에 대한)를 :

WITH CTE AS
(
    SELECT EmpID, EmpName, EmpSalary,
           RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
    FROM dbo.Salary
)
SELECT EmpID, EmpName, EmpSalary
FROM CTE
WHERE RN = @NthRow

---

행 번호 :

SELECT Salary,EmpName
FROM
  (
   SELECT Salary,EmpName,ROW_NUMBER() OVER(ORDER BY Salary) As RowNum
   FROM EMPLOYEE
   ) As A
WHERE A.RowNum IN (2,3)

하위 쿼리 :

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
               SELECT COUNT(DISTINCT(Emp2.Salary))
               FROM Employee Emp2
               WHERE Emp2.Salary > Emp1.Salary
               )

상위 키워드 :

SELECT TOP 1 salary
FROM (
      SELECT DISTINCT TOP n salary
      FROM employee
      ORDER BY salary DESC
      ) a
ORDER BY salary

---

이 시도

SELECT TOP 1 salary FROM (
   SELECT TOP 3 salary 
   FROM employees 
   ORDER BY salary DESC) AS emp 
ORDER BY salary ASC

3의 경우 모든 값을 바꿀 수 있습니다.

---

최적화 방법을 원한다면 TOP키워드 사용을 의미 하므로 nth max 및 min salaries 쿼리는 다음과 같지만 쿼리는 집계 함수 이름을 사용하여 역순으로 까다로운 것처럼 보입니다.

N 최대 급여 :

SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary DESC) 

예 : 3 최대 급여 :

SELECT MIN(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary DESC) 

N 최소 급여 :

SELECT MAX(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP N EmpSalary FROM Salary ORDER BY EmpSalary ASC)

예 : 3 최저 급여 :

SELECT MAX(EmpSalary)
FROM Salary
WHERE EmpSalary IN(SELECT TOP 3 EmpSalary FROM Salary ORDER BY EmpSalary ASC)

---

하위 쿼리를 사용하면 너무 간단합니다!

SELECT MIN(EmpSalary) from (
SELECT EmpSalary from Employee ORDER BY EmpSalary DESC LIMIT 3
);

여기에서 LIMIT 제약 후 n 번째 값만 변경할 수 있습니다.

여기이 하위 쿼리는 EmpSalary DESC Limit 3에 의해 Employee Order에서 EmpSalary를 선택합니다. 직원의 최고 급여 3 개를 반환합니다. 결과에서 직원의 세 번째 TOP 급여를 얻기 위해 MIN 명령을 사용하여 최소 급여를 선택합니다.

---

N을 최대 수로 바꾸십시오.

SELECT *
FROM Employee Emp1
WHERE (N-1) = (
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

설명

위의 쿼리는 이전에 이와 같은 것을 본 적이없는 경우 매우 혼란 스러울 수 있습니다. 내부 쿼리 (서브 쿼리)는 외부 쿼리 (이 경우 Emp1 테이블)의 값을 사용하기 때문에 내부 쿼리는 상관 하위 쿼리라고합니다. ) WHERE 절입니다.

그리고 소스

---

하위 쿼리를 사용하지 않고 급여 테이블의 세 번째 또는 n 번째 최대 급여

select salary from salary
   ORDER   BY salary DESC
   OFFSET  N-1 ROWS
   FETCH NEXT 1 ROWS ONLY

세 번째로 높은 급여의 경우 N-1 대신 2를 입력합니다.

---

SELECT Salary,EmpName
FROM
(
SELECT Salary,EmpName,DENSE_RANK() OVER(ORDER BY Salary DESC) Rno from EMPLOYEE
) tbl
WHERE Rno=3

---

n 번째로 높은 급여를 받으려면 다음 쿼리를 참조하십시오. 이렇게하면 MYSQL에서 n 번째로 높은 급여를받습니다. n 번째로 낮은 급여를 받으려면 쿼리에서 DESC를 ASC로 바꾸면됩니다.

---

방법 1 :

SELECT TOP 1 salary FROM (
SELECT TOP 3 salary 
 FROM employees 
  ORDER BY salary DESC) AS emp 
 ORDER BY salary ASC

방법 2 :

  Select EmpName,salary from
  (
    select EmpName,salary ,Row_Number() over(order by salary desc) as rowid      
     from EmpTbl)
   as a where rowid=3

---

2008 년에는 ROW_NUMBER () OVER (ORDER BY EmpSalary DESC)를 사용하여 동점없이 사용할 수있는 순위를 얻을 수 있습니다.

예를 들어이 방법으로 8 번째로 높은 값을 얻거나 @N을 다른 것으로 변경하거나 원하는 경우 함수의 매개 변수로 사용할 수 있습니다.

DECLARE @N INT = 8;
WITH rankedSalaries AS
(
SELECT
EmpID
,EmpName
,EmpSalary,
,RN = ROW_NUMBER() OVER (ORDER BY EmpSalary DESC)
FROM salary
)
SELECT
EmpID
,EmpName
,EmpSalary
FROM rankedSalaries
WHERE RN = @N;

SQL Server 2012에서는 LAG ()를 사용하여보다 직관적으로 수행됩니다.

---

SELECT EmpSalary 
FROM salary_table 
GROUP BY EmpSalary 
ORDER BY EmpSalary DESC LIMIT n-1, 1;

---

declare @maxNthSal as nvarchar(20)
SELECT TOP 3 @maxNthSal=GRN_NAME FROM GRN_HDR   ORDER BY GRN_NAME DESC
print @maxNthSal

---

이것은 모든 SQL 인터뷰에서 인기있는 질문 중 하나입니다. 열에서 n 번째로 높은 값을 찾기 위해 다른 쿼리를 작성하겠습니다.

아래 스크립트를 실행하여 "Emloyee"라는 테이블을 만들었습니다.

CREATE TABLE Employee([Eid] [float] NULL,[Ename] [nvarchar](255) NULL,[Basic_Sal] [float] NULL)

이제 insert 문을 실행하여이 테이블에 8 개의 행을 삽입하겠습니다.

insert into Employee values(1,'Neeraj',45000)
insert into Employee values(2,'Ankit',5000)
insert into Employee values(3,'Akshay',6000)
insert into Employee values(4,'Ramesh',7600)
insert into Employee values(5,'Vikas',4000)
insert into Employee values(7,'Neha',8500)
insert into Employee values(8,'Shivika',4500)
insert into Employee values(9,'Tarun',9500)

Now we will find out 3rd highest Basic_sal from the above table using different queries. I have run the below query in management studio and below is the result.

select * from Employee order by Basic_Sal desc

We can see in the above image that 3rd highest Basic Salary would be 8500. I am writing 3 different ways of doing the same. By running all three mentioned below queries we will get same result i.e. 8500.

First Way: - Using row number function

select Ename,Basic_sal
from(
            select Ename,Basic_Sal,ROW_NUMBER() over (order by Basic_Sal desc) as rowid from Employee
      )A
where rowid=2

---

Select TOP 1 Salary as '3rd Highest Salary' from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC) a ORDER BY Salary ASC;

I am showing 3rd highest salary

---

SELECT MIN(COLUMN_NAME)
FROM   (
           SELECT DISTINCT TOP 3     COLUMN_NAME
           FROM   TABLE_NAME
           ORDER BY
                  COLUMN_NAME        DESC
       ) AS 'COLUMN_NAME'

---

--nth highest salary

select * 
from (select lstName, salary, row_number() over( order by salary desc) as rn 
      from employee) tmp
where rn = 2

--(nth -1) highest salary

select * 
from employee e1
where 1 = (select count(distinct salary)  
           from employee e2
           where e2.Salary > e1.Salary )

---

Optimized way: Instead of subquery just use limit.

select distinct salary from employee order by salary desc limit nth, 1;

See limit syntax here http://www.mysqltutorial.org/mysql-limit.aspx

---

To get third highest value from table

SELECT * FROM tableName ORDER BY columnName DESC LIMIT 2, 1

---

By subquery:

SELECT salary from
(SELECT rownum ID, EmpSalary salary from
(SELECT DISTINCT EmpSalary from salary_table order by EmpSalary DESC)
where ID = nth)

---

Try this Query

SELECT DISTINCT salary
FROM emp E WHERE
&no =(SELECT COUNT(DISTINCT salary) 
FROM emp WHERE E.salary <= salary)

Put n= which value you want

---

set @n = $n

SELECT a.* FROM ( select a.* , @rn = @rn+1  from EMPLOYEE order by a.EmpSalary desc ) As a  where rn = @n

---

MySQL tested solution, assume N = 4:

select min(CustomerID) from (SELECT distinct CustomerID FROM Customers order by CustomerID desc LIMIT 4) as A;

Another example:

select min(country) from (SELECT distinct country FROM Customers order by country desc limit 3);

---

Try this code :-

SELECT *
   FROM one one1
   WHERE ( n ) = ( SELECT COUNT( one2.salary )
                   FROM one one2
                   WHERE one2.salary >= one1.salary
                 )

---

쿼리에 nth highest bonus, 말을 n=10AdventureWorks2012를 사용하여, 다음 코드를 사용해보십시오

USE AdventureWorks2012; 
GO

SELECT * FROM Sales.SalesPerson;
GO

DECLARE @grade INT;
SET @grade = 10;
SELECT MIN(Bonus)
FROM (SELECT TOP (@grade) Bonus FROM (SELECT DISTINCT(Bonus) FROM Sales.SalesPerson) AS a ORDER BY Bonus DESC) AS g

---

날짜를 기준으로 마지막으로 가장 높은 데이터를 찾는 또 다른 방법

SELECT A.JID,A.EntryDate,RefundDate,Comments,Refund, ActionBy FROM (
(select JID, Max(EntryDate) AS EntryDate from refundrequested GROUP BY JID) A 
Inner JOIN (SELECT JID,ENTRYDATE,refundDate,Comments,refund,ActionBy from refundrequested) B 
ON A.JID=B.JID AND A.EntryDate = B.EntryDate) 

---

// 테이블에서 n '번째 급여를 찾을 수 있습니다. 두 번째로 높은 급여를 검색하려면 n = 2를 입력하고, 3 시간이면 n = 3을 입력합니다.

SELECT *  FROM tablename t1 
WHERE (N-1) = (SELECT COUNT(DISTINCT(t2.Salary)) 
 FROM tablename t2
WHERE t2.Salary > t1.Salary)

---

이것을 시도 할 수 있습니다.

select top(1) EXPORT_NO
from DC_HDR 
order by CASE when  (ROW_NUMBER() over(order by EXPORT_NO desc))=3 then EXPORT_NO else 0 end desc

---

select min(salary) 
from (select salary 
      from employee 
      where rownum < n+1 
      order by salary desc);

---

세 번째로 높은 급여 표시 :

select * from emp where sal=
(SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 3,1) ;

세 번째로 높은 급여 만 표시 :

SELECT DISTINCT sal FROM emp ORDER BY sal DESC LIMIT 3,1

참고 URL : https://stackoverflow.com/questions/16234983/how-to-find-third-or-nth-maximum-salary-from-salary-table