我现在有一个表,如下:
A1 A2 B1 B2
111 50 111 20
121 60 121 100
122 40
135 55
141 15
101 40
151 21
想要得到的结果是:
C1 C2
111 30
121 40
122 40
135 55
141 15
101 40
151 21
即 如果A1=B1时,A1或B1随便取一个,后一个字段的值为|A2-B2|
A1=null时,结果取B1,|A2-B2|
B1=null时,结果取A1,|A2-B2|
请问能用SQL语句实现吗?
A1 A2 B1 B2
111 50 111 20
121 60 121 100
122 40
135 55
141 15
101 40
151 21
想要得到的结果是:
C1 C2
111 30
121 40
122 40
135 55
141 15
101 40
151 21
即 如果A1=B1时,A1或B1随便取一个,后一个字段的值为|A2-B2|
A1=null时,结果取B1,|A2-B2|
B1=null时,结果取A1,|A2-B2|
请问能用SQL语句实现吗?
decode(sign(abs(decode(tt.A1,null,0,tt.A1)-decode(tt.B1,null,0,tt.B1))),0,abs(decode(tt.A2,null,0,tt.A2)-decode(tt.B2,null,0,tt.B2)),1,abs(decode(tt.A2,null,0,tt.A2)-decode(tt.B2,null,0,tt.B2))) as C2
from tablename tt;result: C1 C2
---------- ----------
111 30
121 40
122 40
135 55
141 15
101 40
151 217 rows selected
第2个字段恒为A2-B2的绝对值,不知道是否楼主描述错误.select nvl(A1,B1) as C1,abs(A2-B2) from table;
UNION
SELECT 121 A1,60 A2,121 B1, 100 B2 FROM DUAL
UNION
SELECT 122 A1,40 A2,NULL B1, NULL B2 FROM DUAL
UNION
SELECT 135 A1,55 A2,NULL B1, NULL B2 FROM DUAL
UNION
SELECT 141 A1,15 A2,NULL B1, NULL B2 FROM DUAL
UNION
SELECT NULL A1,NULL A2, 101 B1,40 B2 FROM DUAL
UNION
SELECT NULL A1,NULL A2,151 B1, 21 B2 FROM DUAL
)
select NVL(A1,B1),CASE WHEN A1=B1 THEN ABS(A2-B2)
WHEN A1 IS NULL THEN ABS(NVL(A2,0)-NVL(B2,0))
WHEN B1 IS NULL THEN ABS(NVL(A2,0)-NVL(B2,0)) END C2
FROM A
select nvl(A1,B1) C1,abs(NVL(A2,0)-NVL(B2,0)) C2 from A;
一定要加NVL