SQL查询，查找每个灯具的最高薪资。

Question

由于我不是编写SQL查询的专家，所以需要帮助。我有一个名为程序员的表，其结构和数据类似于：

PNAME,PROF1,PROF2,SALARY

在prof1数据中：

PASCAL，CLIPPER，COBOL，CPP，COBOL，PASCAL，ASSEMBLY，PASCAL，BASIC，C，PASCAL，FOXPRO。

在prof2数据中：

BASIC，COBOL，DBASE，DBASE，ORACLE，DBASE，CLIPPER，C，DBASE，COBOL，ASSEMBLY，BASIC，C.

薪金数据如下：

3200,2800,3000,2900,4500,2500,2800,3000,3200,2500,3600,3700,3500.

我需要一个查询来显示每种语言的最高工资程序员的名字，这意味着我需要显示每种语言的最高工资&人名。我尽力想得到结果，但没有得到答案。你能帮帮我吗?

Answer 1

虽然我喜欢Gordon的答案，但是您可以使用一个普通的表表达式和一个简单的左联接来实现它；

WITH cte AS (
  SELECT PNAME, SALARY, PROF1 PROF FROM programmer
  UNION 
  SELECT PNAME, SALARY, PROF2      FROM programmer
)
SELECT p1.PNAME, p1.PROF, p1.SALARY
FROM cte p1
LEFT JOIN cte p2
  ON p1.PROF = p2.PROF AND p1.SALARY < p2.SALARY
WHERE p2.PNAME IS NULL;

编辑:用于测试的SQLfiddle。

联合将PROF1和PROF2扁平化为单独的行，而左联接基本上是在没有薪酬更高的程序员的情况下找到同样熟练程度的程序员。

Answer 2

函数row_number()是实现这一目标的最佳方法。

select t.*
from (select t.*,
             row_number() over (partition by language order by salary desc) as seqnum
      from t
     ) t
where seqnum = 1;

如果有多个具有相同薪水的程序员，这将返回其中一个。如果您想要所有这些，请使用dense_rank()而不是row_number()。

在重新阅读查询时，我认为“语言”可以是prof1或prof2。这使查询更加复杂。最简单的方法可能是使用一个窗口函数来获得每个函数的最大值，然后比较薪资：

select t.*
from (select t.*,
             max(salary) over (partition by prof1) as max1,
             max(salary) over (partition by prof2) as max2
      from t
     ) t
where (salary = max1 and max1 >= max2) or
      (salary = max2 and max2 >= max1)

Answer 3

您的数据集标准化程度很差，有时会发生，但出于性能原因值得修改。

要获得要查找的查询，您需要派生一组所有不同的语言，为最高工资提取一个标识符，然后用该薪资引用程序员。实现这一点的方法有很多种，最简单的方法可能是使用三个子查询。

SELECT 
  lang.prof,
  (
    SELECT TOP 1 pname
    FROM programmer
    WHERE prof1 = lang.prof OR prof2 = lang.prof
    ORDER BY salary DESC
  ) as Name,
  (
    SELECT MAX(Salary)
    FROM programmer
    WHERE prof1 = lang.prof OR prof2 = lang.prof
  ) as MaxSalary
FROM (
  SELECT prof1 as prof FROM programmer 
  UNION
  SELECT prof2 as prof FROM programmer 
) AS lang

请注意，该查询是而不是优化的，而且远不是查询表的最有效方法。如果可能的话，重新构造您的数据，让程序员、语言和他们的熟练程度在不同的表上。

带有嵌套子查询的更干净的实现。更多查询，但您正在通过直接引用查询Programmer，并且可以添加任意行。

SELECT 
  Lang.prof,
  P.PName,
  P.Salary
FROM 
(
    SELECT LL.prof, MAX(sP.salary) as MaxSalary
    (
      SELECT prof1 as prof FROM programmer 
      UNION
      SELECT prof2 as prof FROM programmer 
    ) as LL
    INNER JOIN programer sP
        ON LL.prof = sP.prof1 OR LL.prof = sP.prof2
    GROUP BY LL.prof
) as Lang
INNER JOIN programmer P
  ON (Lang.prof = P.prof1 OR P.prof2)
  AND lang.MaxSalary = P.salary