Why I submit the codes below, there always occurs the Runtime Error, which I tested on my computer are right.


  • 0
    F

    drop table Employee;
    drop table Department;
    create table Department(Id int primary key, Name varchar(10));
    insert into Department(Id,Name) values(1,'IT'),(2,'Sales');
    create table Employee(Id int primary key auto_increment,Name varchar(10),Salary double,DepartmentId int, foreign key(DepartmentId) references departmentid(Id));
    insert into Employee(Name,Salary,DepartmentId)
    values('Joe',70000,1),('Henry',80000,2),('Sam',60000,2),('Max',90000,1),('Janet',69000,1),('Randy',85000,1);
    select d.name Department,e.name Employee,salary Salary from
    Employee e join Department d on e.departmentid=d.id
    where e.id in
    (
    select id from #get all the id begin
    (
    select * from #f1 begin
    (select id,name,max(salary)
    from(select * from Employee order by salary desc) as f1_1
    group by departmentid) as f1_2 #fi end
    union
    select id,name,max(salary) from #f2 begin
    (select * from Employee
    where id not in
    (select id from
    (select id,name,max(salary) from
    (select * from Employee order by salary desc) as f2_1
    group by departmentid) as f2_2)
    order by salary desc) as f2_3
    group by departmentid #f2 end
    union
    select id,name,max(salary) from #f3 begin
    (select * from Employee
    where id not in
    (select id from
    (select * from
    (select id,name,max(salary)
    from(select * from Employee order by salary desc) as f3_1
    group by departmentid) as f3_2
    union
    select id,name,max(salary) from
    (select * from Employee
    where id not in
    (select id from
    (select id,name,max(salary) from
    (select * from Employee order by salary desc) as f3_3
    group by departmentid) as f3_4)
    order by salary desc) as f3_5
    group by departmentid) as f3_6)
    order by salary desc) as f3_7
    group by departmentid #f3 end
    )
    as ff1 #get all the id end
    )
    order by e.departmentid asc,e.salary desc;


  • 0

    Please format that.


Log in to reply
 

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