This one is OK ,but the SQL is terrible


  • 0
    L
    select 
    	newT.Department,
           name AS Employee,
           newT.Salary
    from
    (SELECT e.id,
              d.name AS Department,
              e.name,
              e.salary
       FROM Department d
       JOIN Employee e ON d.id = e.DepartmentId 
       ORDER BY d.id,
                salary DESC)AS newT RIGHT JOIN
    
    #the top three salaries in each of the department
    (
    	select  Department,salary,
    				@num := if(@type = Department, @num + 1, 1) as row_number,
    				 @type:= Department as dummy
    	from 
    	(SELECT @num := 0, @type := '') r,
    	(
    		select  Department,salary,d_id
    		 from
    		(select e.id,d.name as Department,e.name,e.salary ,d.id as d_id from  Department d join Employee e on d.id = e.DepartmentId  order by d.id , salary DESC ) as c
    		group by d_id,salary 
    		order by d_id,salary DESC
    
    	)as xy  
    	group by d_id,salary
    	having row_number <=3
    	order by d_id,salary DESC
    )as xyz
    
    on newT.Department=xyz.Department and newT.salary = xyz.salary

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.