部门明智需要第二高的salry
问题描述：
我有2张桌子
客户1
Id部门设计
1小时AA
2财务BB
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