test 表中有 num1、num2、num3 3个字段
第一列、第二列、第三列
1 1 1
2 -1 2
3 1 -1
-1 2 1
1 3 2
求每列 -1 之前的最大值
结果是：3、1、2
ok了. 查询每列值为 -1的 前一排最近值, 如果该列中没有-1.则返回0.
select t1.c1, t2.c2, t3.c3
from (select nvl (min(c1),0) c1
from test
where rowid =
(select max(rowid)
from test
where rowid < (select rowid from test where c1 = -1)) order by c1) t1,
(select nvl (min(c2),0) c2
from test
where rowid =
(select max(rowid)
from test
where rowid < (select rowid from test where c2 = -1)) order by c2) t2,
(select nvl (min(c3),0) c3
from test
where rowid =
(select max(rowid)
from test
where rowid < (select rowid from test where c3 = -1)) order by c3) t3
SELECT MAX(第一列),MAX(第二列),MAX(第三列) FROM 表
不知道对不对.
您好。 可以使用下面的方案实现
select greatest(c1,c2,c3) as maxvlaue from tablename
希望您 健康 愉快！
select (greatest(c1,c2,c3) -1 )as maxvlaue from tablename
对不起. 没看到您有要减1的要求
select (greatest(c1,c2,c3) +1 )as maxvlaue from tablename
对不起. 没看到您有要减1的要求
那就是下面的.
SELECT MAX(c1),MAX(c2),MAX(c3)-1 FROM tablename
SELECT MAX(c1),MAX(c2),MAX(c3)-2 FROM tablename
我感觉没有个自动增长的id列不好弄啊,
要是oracle我感觉还好办,
是oracle,我给你个思路,
先确定每列-1所在行的ROWNUM,
然后查小于这个ROWNUM,并且每列最大的值,
select t1.c1 ,t2.c2, t3.c3 from (select min(c1) c1 from (select c1 from test where c1 > -1 order by c1) ) t1,
(select min(c2) c2 from (select c2 from test where c2 > -1 order by c2)) t2 ,
(select min(c3) c3 from (select c3 from test where c3 > -1 order by c3)) t3
可以了. 如果没有-1 就给0 是吧. 这个我等会写. 我先开会了....
select t1.c1, t2.c2, t3.c3
from (select c1
from test
where rowid =
(select max(rowid)
from test
where rowid < (select rowid from test where c1 = -1))) t1,
(select c2
from test
where rowid =
(select max(rowid)
from test
where rowid < (select rowid from test where c2 = -1))) t2,
(select c3
from test
where rowid =
(select max(rowid)
from test
where rowid < (select rowid from test where c3 = -1))) t3