求问一道SQL高频题

求问利扣上一道SQL题：https://leetcode.com/problems/de ... salary/description/我写的是这样：
SELECT D.Name AS Department, E.Name AS Employee, E.Salary
FROM Employee AS E
JOIN Department AS D
ON E.DepartmentId=D.Id
WHERE 1>
(SELECT count(E1.Salary)
FROM Employee AS E1
WHERE E1.Salary> E.Salary
AND E.DepartmentId=E1.DepartmentId
GROUP BY E.DepartmentId);

去掉最后一排的group by才返回正确答案，否则什么也不返回。我知道还有别的优化做法，但为什么我后面加一个GROUP BY E.DepartmentId就不对了？
求大神解答一下，感谢~

SELECT b.Name AS Department, a.Name AS Employee, a.Salary
FROM Employee a
JOIN Department b
ON a.DepartmentId = b.Id
WHERE (a.DepartmentId, a.Salary) in
   (SELECT DepartmentId, Max(Salary) AS Salary
    FROM Employee
    GROUP BY DepartmentId)

我也遇到这个问题了，我用了group by为啥感觉都不对。而且用R或者python来处理数据，思维就是先group再排列比较啊。。。不懂为何不对。求大神解释

是不是leetcode用的都是MySQL 不能用window function啊哭.....

select c.Name as Department, b.Employee, b.Salary
from
(select Name as Employee, Salary, DepartmentId
from
(select *, row_number() over (partition by DepartmentId order by Salary desc) as row_num
from Employee) a
where row_num=1) b
left join Department c
on b.DepartmentId = c.Id

我不是很懂MYSQL的syntax，但我理解的是count()的group by的是来自你外层的D.Name AS Department, E.Name AS Employee, E.Salary，你再加一个GROUP BY E.DepartmentId其实多余，居然没有报语法错误，如果能多跑几个test case可能能知道为什么。因为这种写法，其实是用Employee E cartesian join Employee E1, where E1.Salary> E.Salary AND E.DepartmentId=E1.DepartmentId.   

misslost 发表于 2018-5-25 22:50
是不是leetcode用的都是MySQL 不能用window function啊哭.....

select c.Name as Department, b.Employe ...

leetcode有oracle，oracle有这个window function。你写的都对，但是要用rank() 不能用 row_number()，要不然这道题就是easy了

1. 
   
2. select Department
   
3. ,   Name as Employee
   
4. ,   Salary
   
5. from
   
6. (
   
7. SELECT a.*
   
8. ,   b.Name as Department
   
9. ,   rank() over (partition by b.Name order by a.Salary desc) as rankid
   
10. FROM Employee A
    
11. INNER JOIN Department B
    
12. ON A.DepartmentId = B.Id) x
    
13. where rankid = 1

复制代码

当E.Salary最大时括号里的结果集是空集而不是{0}，这会导致结果集和1无法比较

select
Department.Name as Department,
Employee.Name as Employee,
Employee.Salary as Salary
from Employee join Department on employee.departmentid=department.id
where (employee.departmentid, employee.salary) in (select e2.departmentid, max(e2.salary) from employee e2 group by 1);

已经有了E.DepartmentId=E1.DepartmentId就不需要group by了吧