数据库有一个表 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/
只能说一山更比一山高,要学的东西还多着呢!!!继续加油!