100分求助，很急的求一个存储过程！

[程序猿3000]

我的要求是这样的，其实就是对比一列中的值，比如说先选择出第一行的一个值，然后比较他下面的值，如果是一样的，则结果列就显示00001，如果不一样，结果列就显示00002。
例如：假设我选出业务流水号的第一条数据，DE20121026003001,然后让游标往下比对，查询第二条，如果还是DE20121026003001，则在结果列中全部显示为00001，然后查出第三条，如果第三条和第二条对比，如果不一样，则在结果列中显示00002，
年份 功能名称 业务流水号 凭证号 结果列
20121026 |定期存入(网银)处理 |DE20121026003001 |2012102600001 |00001
20121026 |定期存入(网银)处理 |DE20121026003001 |2012102600001 |00001
20121026 |定期存入(网银1)处理 |DE20121026003001 |2012102600001 |00001
20121026 |客户付款处理 |SE20121026005001 |2012102600002 |00002
20121026 |客户付款处理 |SE20121026005001 |2012102600002 |00002
20121026 |客户付款处理 |SE20121026005003 |2012102600003 |00003
20121026 |客户付款处理 |SE20121026005003 |2012102600003 |00003

[andyguan01_2]

select t3.*, t2.rn From (select t1.业务流水号, t1.rownum rn From tab t1 group by t1.业务流水号 order by t1.业务流水号) t2, tab t3 where t3.业务流水号 = t2.业务流水号 order by t3.业务流水号

[程序猿3000]

t1.rownum rn 在这里过不去啊，兄弟！

[小海葵1]

WITH TEST AS (
SELECT '20121026' AS NF,'定期存入(网银)处理' AS GNMC,'DE20121026003001' AS YWLSH,'2012102600001' AS PZH FROM DUAL
UNION ALL
SELECT '20121026' AS NF,'定期存入(网银)处理' AS GNMC,'DE20121026003001' AS YWLSH,'2012102600001' AS PZH FROM DUAL
UNION ALL
SELECT '20121026' AS NF,'定期存入(网银1)处理' AS GNMC,'DE20121026003001' AS YWLSH,'2012102600001' AS PZH FROM DUAL
UNION ALL
SELECT '20121026' AS NF,'客户付款处理' AS GNMC,'SE20121026005001' AS YWLSH,'2012102600002' AS PZH FROM DUAL
UNION ALL
SELECT '20121026' AS NF,'客户付款处理' AS GNMC,'SE20121026005001' AS YWLSH,'2012102600002' AS PZH FROM DUAL
UNION ALL
SELECT '20121026' AS NF,'客户付款处理' AS GNMC,'SE20121026005003' AS YWLSH,'2012102600003' AS PZH FROM DUAL
UNION ALL
SELECT '20121026' AS NF,'客户付款处理' AS GNMC,'SE20121026005003' AS YWLSH,'2012102600003' AS PZH FROM DUAL
)
SELECT T1.*, T2.JGL
  FROM TEST T1
 INNER JOIN (SELECT LPAD(ROWNUM, 5, '0') AS JGL, YWLSH
               FROM (SELECT DISTINCT YWLSH FROM TEST ORDER BY YWLSH)) T2 ON T1.YWLSH =
                                                                            T2.YWLSH

[程序猿3000]

andyguan01_2 ，谢谢你，我测试一下，如果可以，马上结贴！

[fw0124]

select t.*, to_char(dense_rank() over (order by 业务流水号),'09999') 结果列 from t;

[小海葵1]

引用 6 楼 fw0124 的回复:

select t.*, to_char(dense_rank() over (order by 业务流水号),'09999') 结果列 from t;

妙

[BenChiM888]

为啥要用存储过程，搞不懂，给你个关键字 分析函数 6楼给的sql只是用了其中一个分析函数。

[程序猿3000]

难道没有人指导吗？