50,530
社区成员
发帖
与我相关
我的任务
分享
SELECT HC.*
FROM GRID_SYSDB.HIS_CITYCASES HC
LEFT JOIN GRID_SYSDB.HIS_ACT HA
ON HC.CASE_ID = HA.CASE_ID
WHERE HC.CASE_ID = HA.CASE_ID
AND HA.ACT_ID IN
(SELECT TEMP.ACT_ID
FROM (SELECT HA.CASE_ID,
HA.ACT_ID,
HA.ACT_WILLFINISHDATE,
HA.ACT_NAME,
HAA.ACT_STATUS,
HAP.RES_STATUS
FROM GRID_SYSDB.HIS_ACT HA
LEFT JOIN GRID_SYSDB.HIS_ACT_ABNORMAL HAA
ON HA.ACT_ID = HAA.ACT_ID
LEFT JOIN GRID_SYSDB.HIS_APPROVE HAP
ON HAA.ABNORMAL_ID = HAP.ABNORMAL_ID
WHERE HA.ACT_ID IN (SELECT MAX(HA.ACT_ID)
FROM GRID_SYSDB.HIS_CITYCASES HC
LEFT JOIN GRID_SYSDB.HIS_ACT HA
ON HC.CASE_ID = HA.CASE_ID
LEFT JOIN GRID_SYSDB.HIS_ACT_ABNORMAL HAA
ON HA.ACT_ID = HAA.ACT_ID
WHERE 1 = 1
AND HA.DEPT_ID IN (90)
AND HA.ACT_NAME IN ('专业部门办理')
AND HC.CASE_STATUS != 2
AND (HA.OUT_ROUTE NOT LIKE '%驳回%' OR
HA.OUT_ROUTE IS NULL)
GROUP BY HA.CASE_ID)) TEMP)
AND HC.CASE_STATUS <> 2
AND ((HA.ACT_ETIME IS NULL AND SYSDATE > HA.ACT_WILLFINISHDATE) OR
HA.ACT_WILLFINISHDATE < HA.ACT_ETIME)
AND HA.ACT_WILLFINISHDATE >=
TO_DATE('2016-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND HA.ACT_WILLFINISHDATE <=
TO_DATE('2016-12-14 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
AND HA.ACT_OPERATE IN (0, 1, 2)
AND HA.ACT_STIME >=
TO_DATE('2016-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND HA.ACT_STIME <=
TO_DATE('2016-12-14 23:59:59', 'yyyy-mm-dd hh24:mi:ss');
或者优化一下SQL 这么多的IN
看看不行吧SQL 拆开 在程序里做处理