其中一个TRACE 中的SQL语句是这样的:
SELECT *
FROM (SELECT ROWNUM ROW_ID, TAB.*
FROM (select *
from (SELECT kbh,
klx,
dwdm,
dwmc,
ckr,
LTRIM(MAX(SYS_CONNECT_BY_PATH(wtbmc, '
')),
'
') ickwtblx_dicvalue,
LTRIM(MAX(SYS_CONNECT_BY_PATH(to_char(rq,
'yyyy-MM-dd'),
'
')),
'
') rq
FROM (SELECT a.*,
RANK() OVER(ORDER BY kbh) + ROW_NUMBER() OVER(ORDER BY kbh) rn,
ROW_NUMBER() OVER(PARTITION BY kbh ORDER BY kbh) rm
FROM (SELECT a.*, b.dwmc, c.mc wtbmc
FROM (SELECT t.klx,
t.kbh,
t.rq,
t.wtblx ickwtblx,
DECODE(klx,
'B',
(SELECT dwdm
FROM t_ic_dwglk dw
WHERE dw.kbh =
t.kbh),
'C',
(SELECT dwdm
FROM t_ry_jbxx ry
WHERE ry.zyzbh =
t.kbh),
'A',
(SELECT dwmc
FROM t_ic_gaglk ga
WHERE ga.kbh =
t.kbh),
klx) dwdm,
DECODE(klx,
'B',
(SELECT zrr
FROM t_ic_dwglk dw
WHERE dw.kbh =
t.kbh),
'C',
(SELECT xm
FROM t_ry_jbxx ry
WHERE ry.zyzbh =
t.kbh),
'A',
(SELECT zrr
FROM t_ic_gaglk ga
WHERE ga.kbh =
t.kbh),
klx) ckr
FROM t_ic_ickwtbjl t
WHERE t.tbzt = '0') a,
t_dw_jbxx b,
t_dm_ickwtblx c
WHERE a.dwdm = b.dwdm(+)
and a.ickwtblx = c.dm) a)
START WITH rm = 1
CONNECT BY PRIOR rn = rn - 1
GROUP BY kbh, klx, dwdm, dwmc, ckr)
where 1 = 1
and kbh like :1) TAB)
WHERE ROW_ID <= :2 |