SELECT
<include refid="contractInList" />
FROM
cm_basic_info c
LEFT JOIN (
SELECT
*
FROM
cm_basic_parties
WHERE
flg_accounting = 1
AND flg_own = 2
) p ON c.contract_id = p.contract_id
LEFT JOIN cm_sign s ON c.contract_id = s.cm_id
LEFT JOIN (
SELECT
*
FROM
cm_archive
WHERE
biz_status = '02'
) arc ON c.contract_id = arc.cm_id
WHERE
EXISTS(
SELECT
contract_id
FROM
(
SELECT
creater_date AS create_date,
contract_id
FROM
cm_turn
WHERE
turn_toUser = #{authPersonal}
AND STATUS = '0'
AND biz_status = '02' ##先显示数据为别人移交过来
) xi
where xi.contract_id = c.contract_id
)
and ##排除掉别人移交过来后被自己完整移交出去的
c.contract_id not in (
SELECT
d.contract_id
FROM
(
SELECT
creater_date AS create_date,
contract_id
FROM
cm_turn
WHERE
turn_toUser = #{authPersonal}
AND STATUS = '0'
AND biz_status = '02' ##别人移交过来的所有数据
) d
WHERE
d.contract_id IN (
SELECT
contract_id
FROM
(
SELECT
creater_date AS create_date,
contract_id
FROM
cm_turn
WHERE
turn_user = #{authPersonal}
AND STATUS = '0'
AND biz_status in('01', '02' )
##移交给他人的所有成功数据
) b
WHERE
b.create_date > d.create_date
)
)
这里想要把not in替换掉，但是不能使用not exists，否则返回的结果不对，求教大神该怎么改
