查询工资比领导高的直接下属SQL

为什么80%的码农都做不了架构师？>>>   

1. 建表

   create table salary
   (
     id        number(6) not null,
     parent_id number(6) not null,
     salary    number(6) not null,
     constraint pk_salary primary key (SALARY)
   );

2. 插入数据

   insert into salary (ID, PARENT_ID, SALARY)values (1, 0, 18000);
       insert into salary (ID, PARENT_ID, SALARY)values (3, 1, 12000);
           insert into salary (ID, PARENT_ID, SALARY)values (9, 3, 12500);
           insert into salary (ID, PARENT_ID, SALARY)values (10, 3, 2500);
       insert into salary (ID, PARENT_ID, SALARY)values (4, 1, 9000);
       insert into salary (ID, PARENT_ID, SALARY)values (5, 1, 6000);
   insert into salary (ID, PARENT_ID, SALARY)values (2, 0, 15000);
       insert into salary (ID, PARENT_ID, SALARY)values (6, 2, 16000);
       insert into salary (ID, PARENT_ID, SALARY)values (7, 2, 7000);
       insert into salary (ID, PARENT_ID, SALARY)values (8, 2, 4000);

3. 执行SQL

   select m.*
     from salary m, salary l --员工--领导
    where m.parent_id = l.id
      and m.salary > l.salary

    

总结：关键点是利用表的自连接。

转载于:https://my.oschina.net/h2do/blog/268162