求sql语句，分数不多，望各位大虾帮忙

Question

SQL> select c2 from b;        C2
----------
         2
         3
         47 rows selectedSQL> select (select max(c2) from b where rownum <= t.rn )
  2  from (select c2,rownum rn from b) t
  3  /(SELECTMAX(C2)FROMBWHEREROWNUM
------------------------------
                             2
                             3
                             3
                             3
                             4
                             4
                             47 rows selected

Answer 1

duanzilin(寻)的好像有个问题
如果
C2
----------
         4
         3
         2
可能就不是了

Answer 2

duanzilin(寻) 的SQL是由问题的，你的SQL只适合数字是从小到大排列的行，如果是从大到小，那显示的结果都是最大值 。如：
SQL> select c2 from b;        C2
----------
         5
         3
         47 rows selectedSQL> select (select max(c2) from b where rownum <= t.rn )
  2  from (select c2,rownum rn from b) t
  3  /(SELECTMAX(C2)FROMBWHEREROWNUM
------------------------------
                             5
                             5
                             5
                             5
                             5
                             5
                             57 rows selected

Answer 3

上面写的是针对有序的数列，无序的下面的方法就可以：SQL> select c2 from b;        C2
----------
         5
         3
         47 rows selectedSQL> select max(c2) over(partition by part)
  2  from
  3  (select c2,count(c2) over(order by rownum) part from b)
  4  /MAX(C2)OVER(PARTITIONBYPART)
----------------------------
                           5
                           3
                           3
                           3
                           4
                           4
                           47 rows selected

Answer 4

上面的count是不是应该改为sum?我也没试,大家试了的告诉我在这里用这两个有没有区别?
为NULL的时候COUNT()就不增加了吗?

Answer 5

select decode(ino,'',
(select max(ino) from tb_temp b
where b.iid<a.iid)
,ino) from tb_temp a

Answer 6

duanzilin(寻) ( )佩服死你了