doco LITM VICD 0PSQ GENPO
1 9000020 V64A001100 ZDC 100 0
2 9000020 V64A001100 WXYM 200 1
3 9000020 V64A001100 DHM 300 1
4 9000020 V64A001100 BZ 400 0
5 9000020 V64A001100 HOU 500 1
6 9000020 V64A001100 BDZ 600 0
7 9000020 V64A001100 SYSTEM 700 1
以上是数据表,表主键为 DOCO + OPSQ
要求:如果GENPO等于1,则返回上一记录(OPSQ小于当前记录,但紧临)的WCID字段
即上表应返回的结果应为
WCID
--------------------
ZDC
WXYM
BZ
BDZ
解答:
--创建表
create table MWS_SYS.t513(doco varchar2(10),litm varchar2(20),wcid varchar2(10),opsq integer,genpo int) tablespace "MWS_SYS"
SELECT * FROM MWS_SYS.T513 FOR UPDATE ;
ALTER TABLE MWS_SYS.T513 ADD PRIMARY KEY(DOCO,OPSQ)
--sql语句
select DOCO, LITM, WCID, OPSQ, GENPO, b.rn
from (SELECT DOCO, LITM, WCID, OPSQ, GENPO, ROWNUM as rn FROM MWS_SYS.T513) b,
(SELECT RN - 1 rn
FROM (SELECT GENPO, ROWNUM RN FROM MWS_SYS.T513)
WHERE GENPO = 1) a
where a.rn = b.rn
答案:
DOCO LITM WCID OPSQ GENPO RN
1 9000020 V64A001100 ZDC 100 0 1
2 9000020 V64A001100 WXYM 200 1 2
3 9000020 V64A001100 BZ 400 0 4
4 9000020 V64A001100 BDZ 600 0 6
体会,其实就是两个表连接起来查询到所要结果,涉及到一点rownum的应用