部门明智需要第二高的salry [英] Department wise need 2nd highest salry

问题描述

我有2张桌子

客户1

Id部门设计

1小时AA

2财务BB < br $>
3财务CC

4选举DD



客户2



Id工资

1 10000

2 20000

3 30000

4 40000



我的尝试：



请给我解决方案。我必须使用加入或联合？

I have 2 tables
Customer1
Id Dept Design
1 Hr AA
2 Finance BB
3 Finance CC
4 Elect DD

Customer2

Id Salary
1 10000
2 20000
3 30000
4 40000

What I have tried:

Please give me the solution.I have to use join or union?

推荐答案

加入。

JOIN.

SELECT b.*, a.Salary
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
    FROM Customer2   
) AS a
JOIN Customer1 b ON a.ID = b.ID
WHERE a.RowNum = 2

但是......这是一个非常糟糕的数据库设计。

But ... that's a very poor DB design.

;WITH CTE AS(
SELECT E.*
      ,D.Salary
      ,ROW_NUMBER()OVER(PARTITION BY E.Dept ORDER BY D.Salary DESC) AS RN
  FROM Customer1 AS E INNER JOIN Customer2 AS D
                                      ON (E.ID=D.ID) 
           )

SELECT ID,Dept,Design,Salary FROM CTE WHERE RN=2

这篇关于部门明智需要第二高的salry的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！