sql语句查询等级问题

sql语句查询等级：

表1
id  grade type
1  0  菜鸟
2  200  老鸟
3  400  进阶

表二
c_id  values
1  158
2  203
3  401
4  307

结果

c_id  type
1  菜鸟
2  老鸟
3  进阶
4  老鸟

------解决方法--------------------------------------------------------
修改下，上面那句没法查出进阶

SQL code

select c_id, (select type from 

               (select grade, lead(grade,1) over(order by grade) next_grade, type from t1)

              where t2.values between grade and nvl(next_grade,1000))

from t2;


------解决方法--------------------------------------------------------
with t1 as(select 1 id,0 grade,'菜鸟' type from dual 
 union all select 2,200,'老鸟' from dual 
 union all select 3,400,'进阶' from dual) 
 ,t2 as(select 1 c_id,158 "values" from dual 
 union all select 2,203 from dual 
 union all select 3,401 from dual 
 union all select 4,307 from dual 
 union all select 5,200 from dual) 
   
 select b.*,a.type from t1 a,t2 b 
 where b."values">=a.grade 
 and not exists(select 1 from t1 
 where b."values">=grade 
 and grade>a.grade) 
 order by 1


------解决方法--------------------------------------------------------
SQL code
11:26:35 scott@TUNGKONG> select * from tb1;

        ID      GRADE TYPE

---------- ---------- ----------

         1          0 菜鸟

         2        200 老鸟

         3        400 进阶

已用时间:  00: 00: 00.00

11:26:43 scott@TUNGKONG> select * from tb2;

      C_ID      VALUE

---------- ----------

         1        158

         2        203

         3        401

         4        307

         5        200

已用时间:  00: 00: 00.00

11:26:46 scott@TUNGKONG> select distinct c_id,first_value(type) over(partition by c_id order by grade desc)

11:26:48   2  from (select c_id,type,grade from tb1,tb2 where value >= grade)

11:26:48   3  order by 1;

      C_ID FIRST_VALU

---------- ----------

         1 菜鸟

         2 老鸟

         3 进阶

         4 老鸟

         5 老鸟

已用时间:  00: 00: 00.03