34,576
社区成员
发帖
与我相关
我的任务
分享
SELECT a.* ,
y.PUR_CONT_VERIFY_NO
FROM ( SELECT x.*
FROM PUR_CONT_BASE_INFO x
LEFT JOIN ( SELECT PM.USABLE_STATUS USABLE_STATUSS ,
PM.PROJ_NO ,
PM.PUR_CONT_BASE_INFO_ID PUR_CONT_BASE_INFO_IDS
FROM V_PUR_CONT_LIST_MATE_ALL PM
WHERE PM.USABLE_STATUS = '1'
) M ON x.PUR_CONT_BASE_INFO_ID = M.PUR_CONT_BASE_INFO_IDS
WHERE x.USABLE_STATUS = '1'
AND x.IS_HISTORY_VERSION <> '1'
AND 1 = 1
AND ( M.PROJ_NO = 'ZEC13BA0B00002.121001' )
AND PM.UR_CONT_BASE_INFO_ID IN ( ( SELECT PUR_CONT_BASE_INFO_ID
FROM PUR_CONT_BASE_INFO q --#1.UNION ALL的记录出自同一张表(这部分记录有过滤条件)
WHERE USABLE_STATUS = '1'
AND (q.PUR_MANAGER_CODE LIKE '109910040000000000100001000010000[1,3,4,5,6,7]%'
OR q.PUR_MANAGER_CODE LIKE '1099100400000000001000010000[2,3,4,5]%') --#2.这儿估计是少了一个括号
--UNION ALL
--SELECT PUR_CONT_BASE_INFO_ID
--FROM PUR_CONT_BASE_INFO --#1.UNION ALL的记录出自同一张表(这儿用IN的话,union all前部分的SQL,就可以省略了)
)
)
AND ( ( AUDIT_STATE = '003'
AND CONT_STATE IN ( '001', '002' )
)
OR ( CONT_STATE IN ( '001', '002', '007', '008',
'011', '004' ) )
)
) a
LEFT OUTER JOIN PUR_CONT_LIST_CONTRACT y ON a.PUR_CONT_BASE_INFO_ID = y.PUR_CONT_BASE_INFO_ID
ORDER BY a.CREATE_DATE DESC ,
a.CONT_STATE ASC