SQL查询一个表中类别字段中Max()最大值对应的记录

数据库有一个表 Employee，里面有个点击量字段Salary和一个类别字段DepartmentId以及其它信息字段，

现在要搜出每个类别中Salary最大的那条记录，如果是10个类别，那么结果应该是10条记录，

如果最大Salary有两个相同的则全部保留:

select Department.Name Department, a.Name Employee, Salary from Employee as a   
inner join Department on Department.Id=a.DepartmentId
where  Salary=(select max(b.Salary)  
                  from Employee as b  
                  where a.DepartmentId = b.DepartmentId  
                  )
order by Salary

更高效，很好理解：

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )

如果最大Salary有两个相同的则只要一个:

select  Department.Name Department, a.Name Employee, Salary
from (
    select Name,DepartmentId,Salary from Employee as a where Salary=(
        select max(b.Salary) from Employee as b where a.DepartmentId = b.DepartmentId )
     ) as a  
inner join Department on Department.Id=a.DepartmentId
group by DepartmentId  
order by Salary

（测试环境：MySql）

引用原文地址：http://blog.csdn.net/bushizhuanjia/article/details/6854208

我是在LeetCode中遇到的：https://leetcode.com/problems/department-highest-salary/description/

只能说一山更比一山高，要学的东西还多着呢！！！继续加油！

转载于:https://www.cnblogs.com/EasonDongH/p/8385259.html