用nvl转换把空值为0; max:=a if a<b then max:=b; end if; if max <c then max:=c; end if;
SELECT deminid,ammountid,ammountvalue, (CASE WHEN (CASE WHEN nvl(a,0)-nvl(b,0) >0 THEN nvl(a,0) ELSE nvl(b,0)END) - nvl(c,0)>0 THEN (CASE WHEN nvl(a,0)-nvl(b,0) >0 THEN nvl(a,0) ELSE nvl(b,0)END ELSE nvl(c,0)> END) FROM m
SELECT deminid,ammountid,ammountvalue FROM test1 DEMINID AMMOUNTID AMMOUNTVALUE 1 1 1 2 2 1 3 4 3 1 5 6 SELECT deminid,ammountid,ammountvalue, (CASE WHEN (CASE WHEN deminid-ammountid >0 THEN deminid ELSE ammountid END) - ammountvalue >0 THEN CASE WHEN deminid-ammountid >0 THEN deminid ELSE ammountid END ELSE ammountvalue END)MAXVALUES FROM test1 DEMINID AMMOUNTID AMMOUNTVALUE MAXVALUES 1 1 1 2 2 2 1 3 4 4 3 1 5 6 6
SELECT deminid,ammountid,ammountvalue, greatest(deminid,ammountid,ammountvalue) FROM test1 就能达到4楼的效果再多列,也就往里面填字段而已
不好意思,刚才没说清楚,我是想用一条sql语句来实现,我自己用decode实现了一个,可不敢保证逻辑上完全正确 select m.id, decode(sign(decode(sign(m.a-m.b),1, m.a, m.b) - m.c),1,decode(sign(m.a-m.b),1,m.a,m.b),m.c) num from M m where m.empid not in( select e.id from M e where e.a is null and e.b is null and e.c is null ) 我把三个都为空的过滤掉,然后进行比较;不知道这样行不行,如果不行的话该怎么写呢?请高手指点指点...
把为空的字段变成0 再比较
max:=a
if a<b then
max:=b;
end if;
if max <c then
max:=c;
end if;
(CASE WHEN (CASE WHEN nvl(a,0)-nvl(b,0) >0 THEN nvl(a,0) ELSE nvl(b,0)END) - nvl(c,0)>0 THEN (CASE WHEN nvl(a,0)-nvl(b,0) >0 THEN nvl(a,0) ELSE nvl(b,0)END
ELSE nvl(c,0)> END)
FROM m
1 1 1 2
2 1 3 4
3 1 5 6
SELECT deminid,ammountid,ammountvalue,
(CASE WHEN (CASE WHEN deminid-ammountid >0 THEN deminid ELSE ammountid END) - ammountvalue >0 THEN CASE WHEN deminid-ammountid >0 THEN deminid ELSE ammountid END
ELSE ammountvalue END)MAXVALUES
FROM test1
DEMINID AMMOUNTID AMMOUNTVALUE MAXVALUES
1 1 1 2 2
2 1 3 4 4
3 1 5 6 6
greatest(deminid,ammountid,ammountvalue) FROM test1 就能达到4楼的效果再多列,也就往里面填字段而已
select greatest(1,2,3) from dualresult:3
trim(GREATEST(lpad(' ' || a,20,' '),lpad(' ' || b,20,' '),lpad(' ' || c,20 ,' ')))
select
m.id,
decode(sign(decode(sign(m.a-m.b),1, m.a, m.b) - m.c),1,decode(sign(m.a-m.b),1,m.a,m.b),m.c) num
from
M m
where
m.empid
not in(
select
e.id
from
M e
where e.a is null
and e.b is null
and e.c is null
)
我把三个都为空的过滤掉,然后进行比较;不知道这样行不行,如果不行的话该怎么写呢?请高手指点指点...
而且还有这么个问题:同一个id的记录有多条数据的情况