我想在单个sql语句中找到第二个最高工资和第三个最低工资。 [英] i want to find 2nd maximum salary and third minimum salary in single sql statment..

问题描述

T_ID T_NAME BPS_GRADE SALARY

1 IMRAN 17 30000

2 AHMAD 18 35000

3 KAMRAAN 17 32000

4 ASIF 19 40000

5 TOFEEQ 18 33000

T_ID T_NAME BPS_GRADE SALARY
1 IMRAN 17 30000
2 AHMAD 18 35000
3 KAMRAAN 17 32000
4 ASIF 19 40000
5 TOFEEQ 18 33000

推荐答案

我们不做你的功课：这是有原因的。它就是为了让你思考你被告知的事情，并试着理解它。它也在那里，以便您的导师可以识别您身体虚弱的区域，并将更多的注意力集中在补救措施上。



亲自尝试，你可能会发现它不是和你想的一样困难！



如果遇到具体问题，请询问相关问题，我们会尽力提供帮助。但是我们不打算为你做这一切！

We do not do your homework: it is set for a reason. It is there so that you think about what you have been told, and try to understand it. It is also there so that your tutor can identify areas where you are weak, and focus more attention on remedial action.

Try it yourself, you may find it is not as difficult as you think!

If you meet a specific problem, then please ask about that and we will do our best to help. But we aren't going to do it all for you!

ROW_NUMBER [ ^ ]就是你要找的东西。

ROW_NUMBER[^] is that what you're looking for.

SELECT RowNo, T_ID, T_NAME, BPS_GRADE, SALARY
FROM (
    SELECT T_ID, T_NAME, BPS_GRADE, SALARY, ROW_NUMBER OVER(ORDER BY SALARY DESC) AS RowNo
    FROM YourTableName
) AS T 
WHERE RowNo IN (2,3)

请参阅其他排名功能 [ ^ ]。

尝试以下示例： -



---查找SECONDMAXSAL SALARY

Try Below Example :-

---FIND SECONDMAXSAL SALARY

SELECT MAX(ESAL)AS SECONDMAXSAL FROM EMP1 WHERE ESAL NOT IN(SELECT MAX(ESAL) FROM EMP1)

- 找到第三季度的佣金

--FIND THIRDMAXSAL SALARY

SELECT MIN(ESAL)AS THIRDMAXSAL FROM EMP1 WHERE ESAL IN(SELECT TOP 3 (ESAL) FROM EMP1 ORDER BY ESAL DESC)

这篇关于我想在单个sql语句中找到第二个最高工资和第三个最低工资。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！