sql - 如何用 * 标记薪水最高的员工？- SQL 服务器

Question

我有一个问题 - 这是我的Employee桌子：

Name  | Department  | Salary
------+-------------+-------------
Ram   |IT           |  1000    
Shyam |HR           |  2000    
Sita  |Management   |  5000  
Joe   |IT           |  6000    
Lacy  |HR           |  2000    
Gen   |Management   |  4000  
Maria |IT           |  2000    
Antony|HR           |  3000    
Raman |Management   |  50000  

现在我想要这样的结果：

Department   |   Salary   |
-------------+------------+    
IT           |  20000    
HR           |  30000    
Management   |  50000     |  *

哪里*将用于标记任何部门的最高工资。我尝试了许多不成功的尝试，例如：

Select 
    Salary,
    Case 
       When Salary = Max(Salary) Then '*' 
    End 
From 
    tblEmployees 
Group By 
    Salary

标签： sqlsql-server

Answer 1

你想要一个窗口函数：

Select Department, max(Salary) as salary,
       (case When max(Salary) = max(Max(Salary)) over ()
             Then '*' 
        End)
from tblEmployees
group by department;