表 A
列 code score
1 20
2 30
3 50
4 80表 B
列 lv maxs mins
A 0 30
B 31 50
C 51 100结果
code score lv
1 20 A
2 30 A
3 50 B
4 80 CSQL应该怎么写?数据量大,要动态的,不固定等级。请指点。
列 code score
1 20
2 30
3 50
4 80表 B
列 lv maxs mins
A 0 30
B 31 50
C 51 100结果
code score lv
1 20 A
2 30 A
3 50 B
4 80 CSQL应该怎么写?数据量大,要动态的,不固定等级。请指点。
1 with tba as(
2 select 1 code,20 score from dual
3 union all
4 select 2,30 from dual
5 union all
6 select 3,50 from dual
7 union all
8 select 4,80 from dual),
9 tbb as(
10 select 'A' lv,0 mins,30 maxs from dual
11 union all
12 select 'B',31,50 from dual
13 union all
14 select 'C',51,100 from dual)
15 select tba.*,tbb.lv
16 from tba,tbb
17* where tba.score between tbb.mins and tbb.maxs
SQL> / CODE SCORE L
---------- ---------- -
1 20 A
2 30 A
3 50 B
4 80 C
select a.*,b.lv from a,b where a.score between b.mins and b.maxs;
至于楼主你说的要动态的,不固定等级,能否说详细点?
select 1 code,20 score from dual
union all
select 2,30 from dual
union all
select 3,50 from dual
union all
select 4,80 from dual),
tbb as(--这个就是增加级别的维度啊对结果没什么影响的
select 'A' lv,0 mins,30 maxs from dual
union all
select 'B',31,50 from dual
union all
select 'C',51,70 from dual
union all
select 'D',71,87 from dual
union all
select 'E',88,100 from dual)
select tba.*,tbb.lv
from tba,tbb
where tba.score between tbb.mins and tbb.maxs CODE SCORE L
---------- ---------- -
4 80 D
3 50 B
2 30 A
1 20 A