我的要求是这样的,其实就是对比一列中的值,比如说先选择出第一行的一个值,然后比较他下面的值,如果是一样的,则结果列就显示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
例如:假设我选出业务流水号的第一条数据,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
From (select t1.业务流水号, t1.rownum rn
From tab t1
group by t1.业务流水号
order by t1.业务流水号) t2,
tab t3
where t3.业务流水号 = t2.业务流水号
order by t3.业务流水号
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
6楼给的sql只是用了其中一个分析函数。