select B.Name as Department, A.Name as Employee, A.Salary as Salary
from Employee A join Department B
on B.Id = A.DepartmentId
where 2 >= (select count(distinct(C.Salary)) from Employee as C where C.DepartmentId = A.DepartmentId and C.Salary>A.Salary)
group by B.Name order by Salary desc
This is how I write the code, However I found that "group by B.Name order by Salary desc" makes me can't pass. But I don't know why, its and interesting question. Thanks for your help.
this is my thinking:
and my qq:937184304
Write your MySQL query statement below
#this is my thinking(use oracle):
first you need to count top three max salary of department name.so sql is
select name from employee where DepartmentId=1 and rownum<=3 order by salary desc
so l can search the top 3 of results
l will sort salary by big to small
then will search sales
select name from employee where DepartmentId = 2 and rownum<=2 order by salary desc
then this result is 2 results of count the bigest salary of employees
now l need to merge this two sql
but l see other solutions is write by some variables
l don't know why is doing by this way?
l write 2 sql, l want to share with guys and answer this question.