17,082
社区成员
发帖
与我相关
我的任务
分享
SELECT *
FROM (SELECT ROW_NUMBER() OVER(order by TMPKSNF DESC, TMPKSBH DESC) AS Ro,
T.*
from (SELECT max(ml.mlwsbh) MLWSBH,
ml.bmsah,
JZ.JZSCRXM,
JZ.JZSCRGH,
JZ.CJSJ,
JZ.AJBH,
JZ.WSBH,
JZ.WSMC,
JZ.ZZZT,
JZPZ,
JZSHRBH,
JZSHR,
JZSHSJ,
JZ.JZBH,
jz.ZHXGSJ,
MBMC,
AJMB_BM AJLB_BM,
jz.DWBM CBDW_BM,
JZ.SFSC,
AJZT_F,
(select nvl2(max(wjxh), count(wjxh), null) ||
nvl2(max(wjxh), '-', null) ||
sum(case
when (select to_date(sysdate - configvalue) b
from XY_DZJZ_XTPZ
where configid = 15) < w.jzczsj then
'0'
else
'1'
end) as SFTX
from yx_dzjz_jzmlwj w
left join yx_dzjz_jzml m
on w.mlbh = m.mlbh
where m.mllx = 0
and m.bmsah = ml.bmsah) LSQ,
to_number(replace(replace(lower(substr(max(ml.MLWSBH),
0,
instr(max(ml.MLWSBH),
'/',
1,
1) - 1)),
's',
'-2'),
'm-',
-4)) TMPKSBH,
to_number(substr(max(ml.MLWSBH),
instr(max(ml.MLWSBH), '/', 1, 1) + 1,
decode(instr(max(ml.MLWSBH),
'/',
1,
2),
0,
length(max(ml.MLWSBH)) -
instr(max(ml.MLWSBH), '/', 1, 1),
instr(max(ml.MLWSBH), '/', 1, 2) -
instr(max(ml.MLWSBH), '/', 1, 1) - 1))) TMPKSNF
FROM yx_dzjz_jzml ml
join yx_dzjz_jzjbxx jz
on ml.jzbh = jz.jzbh
where 1 = 1
and ml.sfsc = 'N'
and jz.sfsc = 'N'
and sslbbm = '000000-000004'
group by ml.bmsah,
JZ.JZSCRXM,
JZ.JZSCRGH,
JZ.CJSJ,
JZ.AJBH,
JZ.WSBH,
JZ.WSMC,
JZ.ZZZT,
JZPZ,
JZSHRBH,
JZSHR,
JZSHSJ,
JZ.JZBH,
jz.ZHXGSJ,
MBMC,
AJMB_BM,
jz.DWBM,
JZ.SFSC,
AJZT_F) T
where 1 = 1
and MBMC <> '模式'
and trim(AJLB_BM) = '0701'
and AJZT_F || AJLB_BM in ('0701', '00701', '30701', '40701')
and ZZZT in (2, 3, 4, 5, 6)
and SFSC = 'N'
and trim(CBDW_BM) in (select distinct trim(QXBM)
from xt_dm_qx a
join XT_QX_RYJSFP b
on trim(a.jsbm) = b.jsbm
and trim(a.bmbm) = b.bmbm
and trim(a.dwbm) = b.dwbm
and gh = '0000'
and b.dwbm = '440300000000'
and a.qxlx = 0)
and trim(AJLB_BM) in (select distinct trim(QXBM)
from xt_dm_qx a
join XT_QX_RYJSFP b
on trim(a.jsbm) = b.jsbm
and trim(a.bmbm) = b.bmbm
and trim(a.dwbm) = b.dwbm
and gh = '0000'
and b.dwbm = '440300000000'
and a.qxlx = 1)) TT
WHERE TT.Ro between 1 and 50
select nvl2(max(wjxh), count(wjxh), null) ||
nvl2(max(wjxh), '-', null) ||
sum(case
when (select to_date(sysdate - configvalue) b
from XY_DZJZ_XTPZ
where configid = 15) < w.jzczsj then
'0'
else
'1'
end) as SFTX
from yx_dzjz_jzmlwj w
left join yx_dzjz_jzml m
on w.mlbh = m.mlbh
where m.mllx = 0
and m.bmsah = ml.bmsah