表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 老鸟
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 老鸟
解决方案 »
- 如何 让 oracle 存储一个<BLOB 0 bytes> 数据 而不是NULL 当存储一个0 bytes 文件的时候
- odbc建dsn连接,报错: SQLState=IM004
- ODP.NET批量插入,在after insert的trigger中查询有误
- 棘手的union问题
- 急!触发器调用另一个数据库的过程的问题!在线等待。。。
- 谁有ORACLE的入门学习的课程,分享下
- 关于 create or replace package !
- 急!在线等求个sql
- 访问远程数据库的问题,一个没看见过的怪事,请大家帮忙。
- 如何代入同一个表的值
- 一条oracle 中的sql语句?
- C#连oracle数据库,测试连接时报错:ORA-12154:TNA:....
(select grade, lead(grade,1) over(order by grade) next_grade, type from t1)
where t2.values between grade and next_grade)
from t2;
FROM (SELECT A.ID,
A.GRAND MIN_GRAND,
NVL(B.GRAND, 999999999999) MAX_GRAND,
A.TYPE
FROM 表1 A, (SELECT ID - 1 ID, GRAND, TYPE FROM 表1) B
WHERE A.ID = B.ID(+)) A,
表二 B
WHERE B.VALUES > A.MIN_GRAND
AND B.VALUES <= A.MAX_GRAND
ORDER BY B.C_ID
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;
FROM 表一
WHERE (grade=
(SELECT MAX(grade)
FROM 表一 a
WHERE (grade <=
(SELECT values
FROM 表二))))
这个是我选出等级的方法,有没有人有更好的方法
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
from (select c_id,type,grade from tb1,tb2 where value >= grade)
order by 1;
---------- ---------- ----------
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