部门明智需要第二高的salry

本文介绍了部门明智需要第二高的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