相关数据已经脱敏处理
-- 原SQL
SELECT *
FROM (SELECT a.*,
(SELECT sum(CASE
WHEN (b.confidence < c.THRESHOLD * d.THRESHOLD AND
b.KEY_NAME = d.TYPE_NAME) THEN
1
ELSE
0
END)
FROM tableAAA_detail b,
tableBBB c,
tableBBB_detail d
WHERE c.TYPE_NAME = 'tableAAA'
AND c.ID = d.TYPE_ID
AND b.ANNO_ID = a.ANNO_ID
AND b.RECORD_ID = a.RECORD_ID
AND (a.ACTIVE_FLG <> 1 OR a.ACTIVE_FLG IS NULL)
AND (b.ACTIVE_FLG <> 1 OR b.ACTIVE_FLG IS NULL)
AND b.KEY_NAME = d.TYPE_NAME) AS checkFlg
FROM tableAAA a
WHERE (a.QA_DROP_FLG IS NULL OR a.QA_DROP_FLG <> 1)
ORDER BY a.ACTIVE_FLG ASC,
CASE
WHEN checkFlg > 0 THEN
0
ELSE
1
END ASC) p ;
-- Join改写（不等价）
SELECT a.*,sum(CASE WHEN (b.confidence < c.THRESHOLD * d.THRESHOLD AND
b.KEY_NAME = d.TYPE_NAME) THEN
1
ELSE
0
END) AS checkFlg
FROM tableAAA a join tableAAA_detail b on ( b.ANNO_ID = a.ANNO_ID
AND b.RECORD_ID = a.RECORD_ID)
join tableBBB_detail d on (b.KEY_NAME = d.TYPE_NAME)
join tableBBB c on (c.ID = d.TYPE_ID)
WHERE (a.QA_DROP_FLG IS NULL OR a.QA_DROP_FLG <> 1)
AND (a.ACTIVE_FLG <> 1 OR a.ACTIVE_FLG IS NULL)
AND (b.ACTIVE_FLG <> 1 OR b.ACTIVE_FLG IS NULL)
and c.TYPE_NAME = 'tableAAA'
group by a.ANNO_ID, a.RECORD_ID
/*
ORDER BY a.ACTIVE_FLG ASC,
CASE
WHEN checkFlg > 0 THEN
0
ELSE
1
END ASC
*/
--left join 改写
SELECT a.*,b.checkFlg
from tableAAA a left join (
SELECT b.ANNO_ID,b.RECORD_ID,sum(CASE
WHEN (b.confidence < c.THRESHOLD * d.THRESHOLD AND
b.KEY_NAME = d.TYPE_NAME) THEN
1
ELSE
0
END) AS checkFlg
FROM tableAAA_detail b,
tableBBB c,
tableBBB_detail d
WHERE c.TYPE_NAME = 'tableAAA'
AND c.ID = d.TYPE_ID
AND (b.ACTIVE_FLG <> 1 OR b.ACTIVE_FLG IS NULL)
AND b.KEY_NAME = d.TYPE_NAME
group by b.ANNO_ID,b.RECORD_ID
) b
on (b.ANNO_ID = a.ANNO_ID AND b.RECORD_ID = a.RECORD_ID)
where (a.QA_DROP_FLG IS NULL OR a.QA_DROP_FLG <> 1)
ORDER BY a.ACTIVE_FLG ASC, checkFlg desc