找到来自不同部门的薪酬最高的员工 [英] Find the highest paid employee from different department

问题描述

姓名薪水部门

John 40,000 IT

Sam 50,000 CS

John 30,000 IT





我需要这样的金额



姓名薪水部门

约翰70,000 IT < br $>
Sam 50,000 CS



然后最高工资





所以，答案就是这样......

名称薪资部门

约翰70,000它



在单个查询中



我尝试过：

从员工中选择最高（工资），其中薪水=（按名称从员工组中选择姓名，薪金（工资））按姓名，工资分组

解决方案

GROUP BY不起作用 - 您添加到GROUP列表的列越多，获得的行越多，而不是越少。请参阅： SQL GROUP By和the 列'名称'在选择列表中无效，因为......错误 [ ^ ] - 它解释了GROUP by和聚合函数。



你想要的是使用GROUP BY和JOIN：

  SELECT  a。 [名称]，a。[薪水]，a。部门
  FROM  MyTable a 
  JOIN （ SELECT  MAX（薪水） As 最高，部门 FROM  MyTable  GROUP   BY 部门）b 
  ON  a.Dept = b.Dept&& a.Salary = b.Highest 

你需要的解决方案是上述所有方案的组合，它只会变得更加复杂，因为工资需要加总首先（可能不是最好的数据库设计）。



不幸的是SQL窗口函数（比如 RANK（））不能在 WHERE 子句中使用，因此我们需要另一个子查询或CTE。



这样有效：

;  WITH 排名 AS  
（
  SELECT  [Name]，Dept，TotSalary，RANK（） OVER （ PARTITION   BY 部门 ORDER   BY  TotSalary  DESC ） as  r 
  FROM （ SELECT  [Name]，Dept，SUM（Salary）< span class =code-keyword> AS  TotSalary 
  FROM 员工 GROUP   BY 部门，[名称]）q 
）
  SELECT  [名称]，部门，TotSalary  FROM 排名其中 r =  1  

这个版本首先按部门和名称汇总工资（如果同名的人在不同的部门 - 有一个关于表的提示架构方式）。



然后它支付工资，因为@ Maciej-Los表示，不止一个员工可以获得相同的金额（尽管@ OriginalGriff的解决方案具有类似的效果，因为WHERE子句。



最后所有这些都进入了CTE（可能是一个子查询），这样我们就可以选出最高排名薪水。



，Dept，TotSalary，RANK（） OVER （ ORDER BY TotSalary DESC ） as r
FROM （ SELECT [Name]，Dept，SUM（Salary） AS TotSalary
FROM 员工 GROUP BY 部门，[名称]）q
）
SELECT [Name]，Dept，TotSalary FROM 排名其中 r = 1

您应该使用排名功能 [ ^ ]： RANK（） [ ^ 为什么？因为可以有多个薪水最高的员工。



试试这个：

  SELECT  a。[名称]，a。[薪水]，a。[部门] 
  FROM 员工a  INNER   JOIN  
（
  SELECT  [Name]，[Dept]，[Salary]，RANK（） OVER （ ORDER   BY  [薪水]） AS  [Rank] 
  FROM  employee 
） as  b  ON  a。[Salary] = b。[薪水]  AND  a。[名称] = b。[姓名]  AND  a。[部门] ] = b。[部门] 
  WHERE  b。[Rank] =  1  

Name Salary Dept
John 40,000 IT
Sam 50,000 CS
John 30,000 IT


I need a sum like this

Name salary dept
John 70,000 IT
Sam 50,000 CS

then maximum salary


so , the answer will be like this...
Name Salary Dept
John 70,000 It

In a single query

What I have tried:

select max(salary) from employee  where salary=(select name, sum(salary)  from employee   group by name)group by name,salary 

解决方案

GROUP BY doesn't work like that - the more columns you add to the GROUP list, the more rows you get, not the fewer. See this: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^] - it explains GROUP by and aggregate functions.

What you want is to use GROUP BY and JOIN:

SELECT a.[Name], a.[Salary], a.Dept
FROM MyTable a
JOIN (SELECT MAX(Salary) As Highest, Dept FROM MyTable GROUP BY Dept) b
ON a.Dept = b.Dept && a.Salary = b.Highest

The solution you need is a combination of all of the above, it only gets more complicated because the salaries need to be summed first (probably not the best database design to be honest).

Unfortunately SQL Window functions (like RANK()) cannot be used in a WHERE clause so we need another sub-query or CTE.

This works:

;WITH rankings AS
(
	SELECT [Name], Dept, TotSalary, RANK() OVER (PARTITION BY Dept ORDER BY TotSalary DESC) as r
	FROM (	SELECT [Name], Dept, SUM(Salary) AS TotSalary 
			FROM Employee GROUP BY Dept, [Name]) q 
)
SELECT [Name], Dept, TotSalary FROM rankings where r = 1

This version first sums the salary by department and name (in case people with the same name are in different departments - there's a hint regarding the table schema by the way).

It then RANKs the salaries because, as @Maciej-Los stated, more than one employee could earn the same amount (although @OriginalGriff's solution has a similar effect due to the WHERE clause).

Finally all of that goes into the CTE (it could have been a sub-query) so that we can pick out only the top ranking total salary.

[EDIT - the comments to Solution 3 made me re-read the question and I'v realised that you don't want the highest salary by department, just the highest salary overall. To get that just remove the partition by Dept

;WITH rankings AS
(
	SELECT [Name], Dept, TotSalary, RANK() OVER (ORDER BY TotSalary DESC) as r
	FROM (	SELECT [Name], Dept, SUM(Salary) AS TotSalary 
			FROM Employee GROUP BY Dept, [Name]) q 
)
SELECT [Name], Dept, TotSalary FROM rankings where r = 1

You should one of ranking function[^]: RANK()[^] Why? Because there can be more than one employee with the highest salary.

Try this:

SELECT a.[Name], a.[Salary], a.[Dept]
FROM employee a INNER JOIN
(
    SELECT [Name], [Dept], [Salary], RANK() OVER(ORDER BY [Salary]) AS [Rank]
    FROM employee 
) as b ON a.[Salary] = b.[Salary] AND a.[Name] = b.[Name] AND a.[Dept] = b.[Dept]
WHERE b.[Rank] = 1

这篇关于找到来自不同部门的薪酬最高的员工的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！