问题描述
test 表中有 num1、num2、num3 3个字段第一列、第二列、第三列1112-1231-1-121132求每列 -1 之前的最大值结果是：3、1、2 问题补充：可能是格式的问题，没有说清楚，我把列数据打成横排看看。第一列: 1,2,3,-1,1第二列: 1,-1,1,2,3第三列: 1,2,-1,1,2 求每列 -1 之前的最大值第一列最大值为：3第二列最大值为：1第三列最大值为：3
解决方案
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
解决方案二：
可以了. 如果没有-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
解决方案三：
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
解决方案四：
是oracle,我给你个思路,先确定每列-1所在行的ROWNUM,然后查小于这个ROWNUM,并且每列最大的值,
解决方案五：
我感觉没有个自动增长的id列不好弄啊,要是oracle我感觉还好办,
解决方案六：
那就是下面的.SELECT MAX(c1),MAX(c2),MAX(c3)-1 FROM tablename SELECT MAX(c1),MAX(c2),MAX(c3)-2 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 greatest(c1,c2,c3) as maxvlaue from tablename希望您 健康 愉快！
解决方案十：
SELECT MAX(第一列),MAX(第二列),MAX(第三列) FROM 表不知道对不对.