SELECT a,b FROM( SELECT a,b,CASE WHEN b =0 AND a >=0 AND a<=10 THEN '1' WHEN b=1 AND a >10 AND a<=100 THEN '1' WHEN b=3 AND a >100 AND a<=1000 THEN '1' ELSE '0' END type_id FROM T) WHERE type_id='1'这样可以
大哥,是输出A啊,A有具体的记录的,意思就是B不同的值下输出A
我还以为group by呢,后细看我也迷糊了
select a,b ,(case when b=0 and (a >=0 and a<10) then 'A的范围1' when b=1 and (a >=10 and a<100) then 'A的范围2' when b=3 and (a >=100 and a<1000) then 'A的范围3' end ) aa from table_name
SQL> with t as( 2 select 1 a,0 b from dual union all 3 select 2,0 from dual union all 4 select 15,1 from dual union all 5 select 56,1 from dual union all 6 select 120,3 from dual) 7 select a,b, 8 (case when b=0 and (a>=0 and a<10) then 'a的范围:0-10' else null end) A1, 9 (case when b=1 and (a>=10 and a<100) then 'a的范围:10-100' else null end) A2, 10 (case when b=3 and (a>=100 and a<1000) then 'a的范围:100-1000' else null end) A3 11 from t; A B A1 A2 A3 ---------- ---------- ------------ -------------- ---------------- 1 0 a的范围:0-10 2 0 a的范围:0-10 15 1 a的范围:10-100 56 1 a的范围:10-100 120 3 a的范围:100-1000
(b = 0 and a between 0 and 10)
or
(b = 1 and a between 10 and 100)
or
(b = 3 and a between 100 and 1000)
一张表T中的有两个字段A,B
字段B有3种值(0,1,3)
B=0时,A的范围为0-10,
B=1时,A的范围为10-100,
B=3时,A的范围为100-1000,
要求在B的3种情况下同时输出A
如:A1 A2 A3
.........
.........
SELECT a,b,CASE WHEN b =0 AND a >=0 AND a<=10 THEN '1'
WHEN b=1 AND a >10 AND a<=100 THEN '1'
WHEN b=3 AND a >100 AND a<=1000 THEN '1'
ELSE '0' END type_id
FROM T) WHERE type_id='1'这样可以
when b=1 and (a >=10 and a<100) then 'A的范围2'
when b=3 and (a >=100 and a<1000) then 'A的范围3' end ) aa
from table_name
SQL> with t as(
2 select 1 a,0 b from dual union all
3 select 2,0 from dual union all
4 select 15,1 from dual union all
5 select 56,1 from dual union all
6 select 120,3 from dual)
7 select a,b,
8 (case when b=0 and (a>=0 and a<10) then 'a的范围:0-10' else null end) A1,
9 (case when b=1 and (a>=10 and a<100) then 'a的范围:10-100' else null end) A2,
10 (case when b=3 and (a>=100 and a<1000) then 'a的范围:100-1000' else null end) A3
11 from t; A B A1 A2 A3
---------- ---------- ------------ -------------- ----------------
1 0 a的范围:0-10
2 0 a的范围:0-10
15 1 a的范围:10-100
56 1 a的范围:10-100
120 3 a的范围:100-1000