SELECT T.accname,
T.certinum,
T.accnum,
T.accphone,
T.uintaccname,
T.UNITACCNUM,
T.deputyname,
T.UNITPHONE,
T20.AJLXMC,
T21.AJSYMC,
U.USERNAME,
D.ORGNAME,
TO_CHAR(T.ACCEPTANCEDATE, 'yyyy-mm-dd'),
T.acceptanceid,
(SELECT U.USERNAME FROM ZF_USER U WHERE U.LOGINID = TAB.OPENOPERID) OPENOPERID,
CASE T2.REGISTER
WHEN '0' THEN
'否'
WHEN '1' THEN
'是'
ELSE
''
END,
TO_CHAR(T2.ACCEPTANCEDATE, 'yyyy-mm-dd'),
TO_CHAR(T2.MODIDATE, 'yyyy-mm-dd'),
(SELECT flowstepname FROM zf102 WHERE flowid = UV.CURFLOW) flowstepname,
CASE T2.CASSTAT
WHEN '0' THEN
'不立案'
WHEN '1' THEN
'立案中'
WHEN '2' THEN
'已立案'
WHEN '3' THEN
'执法中'
WHEN '4' THEN
'执法完成'
ELSE
''
END,
CASE T2.ASTAT
WHEN '0' THEN
'正常'
WHEN '1' THEN
'中止'
WHEN '2' THEN
'移交'
WHEN '7' THEN
'撤消'
WHEN '8' THEN
'退回'
WHEN '9' THEN
'办结'
ELSE
''
END
FROM ZF001 T,
ZF002 T2,
ZF_USER U,
ZF_DEPARTMENT D,
ZF008 UV,
ZF020 T20,
ZF021 T21,
ZF102 Z1,
(SELECT T.ISID, T3.ACCEPTANCEID, T.OPENOPERID, U.USERNAME
FROM ZF010 T, ZF003 T3, ZF_USER U
WHERE T.ISID IN (SELECT MAX(T.ISID)
FROM ZF010 T, ZF003 T3
WHERE T3.ISID = T.ISID
GROUP BY T3.ACCEPTANCEID)
AND T3.ISID = T.ISID
AND T.OPENOPERID = U.LOGINID) TAB
WHERE T.ACCEPTANCEID = T2.ACCEPTANCEID
AND TAB.ACCEPTANCEID = T.ACCEPTANCEID
AND UV.ACCEPTANCEID = T.ACCEPTANCEID
AND T.OPERID = U.LOGINID
AND Z1.FLOWID = T2.FLOWID
AND T.accepttype = T20.AJLXID
AND T21.AJLXID = T20.AJLXID
AND T.REASON = T21.AJSYID
AND DECODE(T2.OPERORGID, NULL, T.PRGID, T2.OPERORGID) = D.ORGID
AND DECODE(T2.OPERORGID, NULL, T.PRGID, T2.OPERORGID) = '1001'
AND UV.SENDTIME1 >= TO_DATE('2018-01-01', 'YYYY-MM-DD hh24:mi:ss')
AND UV.SENDTIME1 <= TO_DATE('2019-01-01', 'YYYY-MM-DD hh24:mi:ss') + 1
SELECT T.accname,
T.certinum,
T.accnum,
T.accphone,
T.uintaccname,
T.UNITACCNUM,
T.deputyname,
T.UNITPHONE,
T20.AJLXMC,
T21.AJSYMC,
U.USERNAME,
D.ORGNAME,
TO_CHAR(T.ACCEPTANCEDATE, 'yyyy-mm-dd'),
T.acceptanceid,
(SELECT U.USERNAME FROM ZF_USER U WHERE U.LOGINID = TAB.OPENOPERID) OPENOPERID,
CASE T2.REGISTER
WHEN '0' THEN
'否'
WHEN '1' THEN
'是'
ELSE
''
END,
TO_CHAR(T2.ACCEPTANCEDATE, 'yyyy-mm-dd'),
TO_CHAR(T2.MODIDATE, 'yyyy-mm-dd'),
(SELECT flowstepname FROM zf102 WHERE flowid = UV.CURFLOW) flowstepname,
CASE T2.CASSTAT
WHEN '0' THEN
'不立案'
WHEN '1' THEN
'立案中'
WHEN '2' THEN
'已立案'
WHEN '3' THEN
'执法中'
WHEN '4' THEN
'执法完成'
ELSE
''
END,
CASE T2.ASTAT
WHEN '0' THEN
'正常'
WHEN '1' THEN
'中止'
WHEN '2' THEN
'移交'
WHEN '7' THEN
'撤消'
WHEN '8' THEN
'退回'
WHEN '9' THEN
'办结'
ELSE
''
END
FROM ZF001 T,
ZF002 T2,
ZF_USER U,
ZF_DEPARTMENT D,
ZF008 UV,
ZF020 T20,
ZF021 T21,
ZF102 Z1,
(SELECT T.ISID, T3.ACCEPTANCEID, T.OPENOPERID, U.USERNAME
FROM ZF010 T, ZF003 T3, ZF_USER U
WHERE T.ISID IN (SELECT MAX(T.ISID)
FROM ZF010 T, ZF003 T3
WHERE T3.ISID = T.ISID
GROUP BY T3.ACCEPTANCEID)
AND T3.ISID = T.ISID
AND T.OPENOPERID = U.LOGINID) TAB
WHERE T.ACCEPTANCEID = T2.ACCEPTANCEID
AND TAB.ACCEPTANCEID = T.ACCEPTANCEID
AND UV.ACCEPTANCEID = T.ACCEPTANCEID
AND T.OPERID = U.LOGINID
AND Z1.FLOWID = T2.FLOWID
AND T.accepttype = T20.AJLXID
AND T21.AJLXID = T20.AJLXID
AND T.REASON = T21.AJSYID
AND DECODE(T2.OPERORGID, NULL, T.PRGID, T2.OPERORGID) = D.ORGID
AND DECODE(T2.OPERORGID, NULL, T.PRGID, T2.OPERORGID) = '1001'
AND UV.SENDTIME2 >= TO_DATE('2018-01-01', 'YYYY-MM-DD hh24:mi:ss')
AND UV.SENDTIME2 <= TO_DATE('2019-01-01', 'YYYY-MM-DD hh24:mi:ss') + 1SELECT T.accname,
T.certinum,
T.accnum,
T.accphone,
T.uintaccname,
T.UNITACCNUM,
T.deputyname,
T.UNITPHONE,
T20.AJLXMC,
T21.AJSYMC,
U.USERNAME,
D.ORGNAME,
TO_CHAR(T.ACCEPTANCEDATE, 'yyyy-mm-dd'),
T.acceptanceid,
(SELECT U.USERNAME FROM ZF_USER U WHERE U.LOGINID = TAB.OPENOPERID) OPENOPERID,
CASE T2.REGISTER
WHEN '0' THEN
'否'
WHEN '1' THEN
'是'
ELSE
''
END,
TO_CHAR(T2.ACCEPTANCEDATE, 'yyyy-mm-dd'),
TO_CHAR(T2.MODIDATE, 'yyyy-mm-dd'),
(SELECT flowstepname FROM zf102 WHERE flowid = UV.CURFLOW) flowstepname,
CASE T2.CASSTAT
WHEN '0' THEN
'不立案'
WHEN '1' THEN
'立案中'
WHEN '2' THEN
'已立案'
WHEN '3' THEN
'执法中'
WHEN '4' THEN
'执法完成'
ELSE
''
END,
CASE T2.ASTAT
WHEN '0' THEN
'正常'
WHEN '1' THEN
'中止'
WHEN '2' THEN
'移交'
WHEN '7' THEN
'撤消'
WHEN '8' THEN
'退回'
WHEN '9' THEN
'办结'
ELSE
''
END
FROM ZF001 T,
ZF002 T2,
ZF_USER U,
ZF_DEPARTMENT D,
ZF008 UV,
ZF020 T20,
ZF021 T21,
ZF102 Z1,
(SELECT T.ISID, T3.ACCEPTANCEID, T.OPENOPERID, U.USERNAME
FROM ZF010 T, ZF003 T3, ZF_USER U
WHERE T.ISID IN (SELECT MAX(T.ISID)
FROM ZF010 T, ZF003 T3
WHERE T3.ISID = T.ISID
GROUP BY T3.ACCEPTANCEID)
AND T3.ISID = T.ISID
AND T.OPENOPERID = U.LOGINID) TAB
WHERE T.ACCEPTANCEID = T2.ACCEPTANCEID
AND TAB.ACCEPTANCEID = T.ACCEPTANCEID
AND UV.ACCEPTANCEID = T.ACCEPTANCEID
AND T.OPERID = U.LOGINID
AND Z1.FLOWID = T2.FLOWID
AND T.accepttype = T20.AJLXID
AND T21.AJLXID = T20.AJLXID
AND T.REASON = T21.AJSYID
AND DECODE(T2.OPERORGID, NULL, T.PRGID, T2.OPERORGID) = D.ORGID
AND DECODE(T2.OPERORGID, NULL, T.PRGID, T2.OPERORGID) = '1001'
AND UV.SENDTIME5 >= TO_DATE('2018-01-01', 'YYYY-MM-DD hh24:mi:ss')
AND UV.SENDTIME5 <= TO_DATE('2019-01-01', 'YYYY-MM-DD hh24:mi:ss') + 1
T.certinum,
T.accnum,
T.accphone,
T.uintaccname,
T.UNITACCNUM,
T.deputyname,
T.UNITPHONE,
T20.AJLXMC,
T21.AJSYMC,
U.USERNAME,
D.ORGNAME,
TO_CHAR(T.ACCEPTANCEDATE, 'yyyy-mm-dd'),
T.acceptanceid,
(SELECT U.USERNAME FROM ZF_USER U WHERE U.LOGINID = TAB.OPENOPERID) OPENOPERID,
CASE T2.REGISTER
WHEN '0' THEN
'否'
WHEN '1' THEN
'是'
ELSE
''
END,
TO_CHAR(T2.ACCEPTANCEDATE, 'yyyy-mm-dd'),
TO_CHAR(T2.MODIDATE, 'yyyy-mm-dd'),
(SELECT flowstepname FROM zf102 WHERE flowid = UV.CURFLOW) flowstepname,
CASE T2.CASSTAT
WHEN '0' THEN
'不立案'
WHEN '1' THEN
'立案中'
WHEN '2' THEN
'已立案'
WHEN '3' THEN
'执法中'
WHEN '4' THEN
'执法完成'
ELSE
''
END,
CASE T2.ASTAT
WHEN '0' THEN
'正常'
WHEN '1' THEN
'中止'
WHEN '2' THEN
'移交'
WHEN '7' THEN
'撤消'
WHEN '8' THEN
'退回'
WHEN '9' THEN
'办结'
ELSE
''
END
FROM ZF001 T,
ZF002 T2,
ZF_USER U,
ZF_DEPARTMENT D,
ZF008 UV,
ZF020 T20,
ZF021 T21,
ZF102 Z1,
(SELECT T.ISID, T3.ACCEPTANCEID, T.OPENOPERID, U.USERNAME
FROM ZF010 T, ZF003 T3, ZF_USER U
WHERE T.ISID IN (SELECT MAX(T.ISID)
FROM ZF010 T, ZF003 T3
WHERE T3.ISID = T.ISID
GROUP BY T3.ACCEPTANCEID)
AND T3.ISID = T.ISID
AND T.OPENOPERID = U.LOGINID) TAB
WHERE T.ACCEPTANCEID = T2.ACCEPTANCEID
AND TAB.ACCEPTANCEID = T.ACCEPTANCEID
AND UV.ACCEPTANCEID = T.ACCEPTANCEID
AND T.OPERID = U.LOGINID
AND Z1.FLOWID = T2.FLOWID
AND T.accepttype = T20.AJLXID
AND T21.AJLXID = T20.AJLXID
AND T.REASON = T21.AJSYID
AND DECODE(T2.OPERORGID, NULL, T.PRGID, T2.OPERORGID) = D.ORGID
AND DECODE(T2.OPERORGID, NULL, T.PRGID, T2.OPERORGID) = '1001'
AND UV.SENDTIME1 >= TO_DATE('2018-01-01', 'YYYY-MM-DD hh24:mi:ss')
AND UV.SENDTIME1 <= TO_DATE('2019-01-01', 'YYYY-MM-DD hh24:mi:ss') + 1
SELECT T.accname,
T.certinum,
T.accnum,
T.accphone,
T.uintaccname,
T.UNITACCNUM,
T.deputyname,
T.UNITPHONE,
T20.AJLXMC,
T21.AJSYMC,
U.USERNAME,
D.ORGNAME,
TO_CHAR(T.ACCEPTANCEDATE, 'yyyy-mm-dd'),
T.acceptanceid,
(SELECT U.USERNAME FROM ZF_USER U WHERE U.LOGINID = TAB.OPENOPERID) OPENOPERID,
CASE T2.REGISTER
WHEN '0' THEN
'否'
WHEN '1' THEN
'是'
ELSE
''
END,
TO_CHAR(T2.ACCEPTANCEDATE, 'yyyy-mm-dd'),
TO_CHAR(T2.MODIDATE, 'yyyy-mm-dd'),
(SELECT flowstepname FROM zf102 WHERE flowid = UV.CURFLOW) flowstepname,
CASE T2.CASSTAT
WHEN '0' THEN
'不立案'
WHEN '1' THEN
'立案中'
WHEN '2' THEN
'已立案'
WHEN '3' THEN
'执法中'
WHEN '4' THEN
'执法完成'
ELSE
''
END,
CASE T2.ASTAT
WHEN '0' THEN
'正常'
WHEN '1' THEN
'中止'
WHEN '2' THEN
'移交'
WHEN '7' THEN
'撤消'
WHEN '8' THEN
'退回'
WHEN '9' THEN
'办结'
ELSE
''
END
FROM ZF001 T,
ZF002 T2,
ZF_USER U,
ZF_DEPARTMENT D,
ZF008 UV,
ZF020 T20,
ZF021 T21,
ZF102 Z1,
(SELECT T.ISID, T3.ACCEPTANCEID, T.OPENOPERID, U.USERNAME
FROM ZF010 T, ZF003 T3, ZF_USER U
WHERE T.ISID IN (SELECT MAX(T.ISID)
FROM ZF010 T, ZF003 T3
WHERE T3.ISID = T.ISID
GROUP BY T3.ACCEPTANCEID)
AND T3.ISID = T.ISID
AND T.OPENOPERID = U.LOGINID) TAB
WHERE T.ACCEPTANCEID = T2.ACCEPTANCEID
AND TAB.ACCEPTANCEID = T.ACCEPTANCEID
AND UV.ACCEPTANCEID = T.ACCEPTANCEID
AND T.OPERID = U.LOGINID
AND Z1.FLOWID = T2.FLOWID
AND T.accepttype = T20.AJLXID
AND T21.AJLXID = T20.AJLXID
AND T.REASON = T21.AJSYID
AND DECODE(T2.OPERORGID, NULL, T.PRGID, T2.OPERORGID) = D.ORGID
AND DECODE(T2.OPERORGID, NULL, T.PRGID, T2.OPERORGID) = '1001'
AND UV.SENDTIME2 >= TO_DATE('2018-01-01', 'YYYY-MM-DD hh24:mi:ss')
AND UV.SENDTIME2 <= TO_DATE('2019-01-01', 'YYYY-MM-DD hh24:mi:ss') + 1SELECT T.accname,
T.certinum,
T.accnum,
T.accphone,
T.uintaccname,
T.UNITACCNUM,
T.deputyname,
T.UNITPHONE,
T20.AJLXMC,
T21.AJSYMC,
U.USERNAME,
D.ORGNAME,
TO_CHAR(T.ACCEPTANCEDATE, 'yyyy-mm-dd'),
T.acceptanceid,
(SELECT U.USERNAME FROM ZF_USER U WHERE U.LOGINID = TAB.OPENOPERID) OPENOPERID,
CASE T2.REGISTER
WHEN '0' THEN
'否'
WHEN '1' THEN
'是'
ELSE
''
END,
TO_CHAR(T2.ACCEPTANCEDATE, 'yyyy-mm-dd'),
TO_CHAR(T2.MODIDATE, 'yyyy-mm-dd'),
(SELECT flowstepname FROM zf102 WHERE flowid = UV.CURFLOW) flowstepname,
CASE T2.CASSTAT
WHEN '0' THEN
'不立案'
WHEN '1' THEN
'立案中'
WHEN '2' THEN
'已立案'
WHEN '3' THEN
'执法中'
WHEN '4' THEN
'执法完成'
ELSE
''
END,
CASE T2.ASTAT
WHEN '0' THEN
'正常'
WHEN '1' THEN
'中止'
WHEN '2' THEN
'移交'
WHEN '7' THEN
'撤消'
WHEN '8' THEN
'退回'
WHEN '9' THEN
'办结'
ELSE
''
END
FROM ZF001 T,
ZF002 T2,
ZF_USER U,
ZF_DEPARTMENT D,
ZF008 UV,
ZF020 T20,
ZF021 T21,
ZF102 Z1,
(SELECT T.ISID, T3.ACCEPTANCEID, T.OPENOPERID, U.USERNAME
FROM ZF010 T, ZF003 T3, ZF_USER U
WHERE T.ISID IN (SELECT MAX(T.ISID)
FROM ZF010 T, ZF003 T3
WHERE T3.ISID = T.ISID
GROUP BY T3.ACCEPTANCEID)
AND T3.ISID = T.ISID
AND T.OPENOPERID = U.LOGINID) TAB
WHERE T.ACCEPTANCEID = T2.ACCEPTANCEID
AND TAB.ACCEPTANCEID = T.ACCEPTANCEID
AND UV.ACCEPTANCEID = T.ACCEPTANCEID
AND T.OPERID = U.LOGINID
AND Z1.FLOWID = T2.FLOWID
AND T.accepttype = T20.AJLXID
AND T21.AJLXID = T20.AJLXID
AND T.REASON = T21.AJSYID
AND DECODE(T2.OPERORGID, NULL, T.PRGID, T2.OPERORGID) = D.ORGID
AND DECODE(T2.OPERORGID, NULL, T.PRGID, T2.OPERORGID) = '1001'
AND UV.SENDTIME5 >= TO_DATE('2018-01-01', 'YYYY-MM-DD hh24:mi:ss')
AND UV.SENDTIME5 <= TO_DATE('2019-01-01', 'YYYY-MM-DD hh24:mi:ss') + 1
可能问题是 你的 in(后面结果有null值,导致结果不对)