Query by Counting Smaller and Larger Elements.


  • 0
    F
    # Write your MySQL query statement below
    select T1.Id, T1.Company, T1.Salary
    from 
    (select count(t2.id) as c1, t1.id, t1.Salary, t1.Company
    from Employee as t1 left join Employee as t2
    on (t1.Salary < t2.Salary or t1.Salary = t2.Salary and t1.Id < t2.Id) and t1.Company = t2.Company
    group by t1.id
    ) as T1 
    join
    (select count(t2.id) as c2, t1.id, t1.Salary, t1.Company
    from Employee as t1 left join Employee as t2
    on (t1.Salary > t2.Salary or t1.Salary = t2.Salary and t1.Id > t2.Id) and t1.Company = t2.Company
    group by t1.id) as T2 
    on T1.id = T2.id
    join
    (select count(id) as c3, Company
    from Employee
    group by Company) as T3 
    on T1.Company = T3.Company
    where T1.c1 <= T3.c3/2 and T2.c2 <= T3.c3/2
    order by Company, Salary
    

Log in to reply
 

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