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.0011:26:43 scott@TUNGKONG> select * from tb2;      C_ID      VALUE---------- ----------         1        158         2        203         3        401         4        307         5        200已用时间:  00: 00: 00.0011: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