面试题如何入手，求解 thx

bingfengguan

bell6248 发表于 2016-9-29 14:13
如下：

SQL> with t as

再次感谢，
case when prior salary < salary then 1 else 0 end flag 佩服，之前用递归卡死在这了

bingfengguan

kelis2004 发表于 2016-9-29 15:04
SELECT * FROM tmp  t1
WHERE t1.salary >ALL (SELECT salary FROM tmp t2 WHERE t1.relation =t2.id)
AN ...

再次感谢，之前怎么也想不到如何建立联系，原来是可以这样创造的，
真的要脑补了。。

lizhiyuan666

select id
  from employee t
where t.relation is not null
   and exists (select 1
          from employee t2
         where t2.id = t.relation
           and t2.salary < t.salary)

sky850623

select * from t_emp a
  where  exists (select * from t_emp b where a.salary>b.salary  and b.relation is null)

260600sz

with tb as
(select 3 id, null relation, '张三' name, 800 sal
    from dual
  union all
  select 1 id, 3 relation, 'jack', 700 sal
    from dual
  union all
  select 4 id, null relation, '李四', 800 sal
    from dual
  union all
  select 2 id, 4 relation, ' shisi', 900 sal
    from dual)
select id,name,relation,sal from (
select t1.id,
       t1.name,
       t1.relation,
       t1.sal,
       case
         when exists (select 1
                 from tb
                where tb.id = t1.relation
                start with relation is null
               connect by prior id = relation
               ) then lag(t1.sal)over(order by rownum) end new_sal
  from tb t1)
  where sal>new_sal
c:\users\administrator\桌面\20161007

260600sz