SELECT d.*, GROUP_CONCAT(DISTINCT wc_person_name) AS 人才选择
FROM (
SELECT
suser.realName AS 所属人,
base.id AS baseid,
luser.realName AS 录入人,
resuser.realName AS 申请人,
sdept.d4 AS 大区,
sdept.d5 AS 分公司,
CONCAT_WS('/', sdept.d4, sdept.d5, sdept.d6, sdept.d7) AS 部门,
contract_no,
price AS 合同金额,
kickback AS 咨询费,
FROM_UNIXTIME(contract_signing_time) AS 签约时间,
CASE
WHEN base.state = 2 THEN '已签约'
WHEN base.state = 3 THEN '合同执行中'
END AS 合同状态,
ent_name AS 企业名称,
ent_linkman AS 联系人,
config.name AS 操作状态,
config2.name AS 核款状态,
FROM_UNIXTIME(xxf.create_time) AS 申请时间,
xxf.transfer_price AS 转业绩金额,
(
SELECT SUM(transfer_price)
FROM kl_conm_account
WHERE qconmid = base.id
AND state IN (12, 14)
AND conmid = 0
) AS 入账总金额,
base.id,
wi.wc_person_name
FROM ods_oa.kl_conm_base base
LEFT JOIN ods_oa.kl_users suser ON base.ouid = suser.ID
LEFT JOIN ods_oa.kl_users luser ON base.uid = luser.ID
LEFT JOIN ods_oa.dept_ext sdept ON sdept.id = suser.DEPARTMENTID
LEFT JOIN kl_conm_ycenterprise qy ON qy.conmid = base.id
LEFT JOIN kl_conm_account_xxf xxf ON xxf.conmid = base.id
LEFT JOIN kl_conm_account_result res ON res.cid = base.id AND is_zx = 2
LEFT JOIN kl_status_config config ON config.id = xxf.state
LEFT JOIN kl_status_config config2 ON config2.id = entry_status
LEFT JOIN ods_oa.kl_users resuser ON resuser.id = xxf.uid
LEFT JOIN kl_conm_ycenterprise_withcert wi ON wi.conmid = base.id
WHERE suser.realName IN (
SELECT 所属人
FROM (
SELECT suser.realName 所属人
FROM ods_oa.kl_conm_base base
LEFT JOIN ods_oa.kl_users suser ON base.ouid = suser.ID
LEFT JOIN kl_conm_account_xxf xxf ON xxf.conmid = base.id
WHERE contract_no LIKE '%Q%'
AND xxf.transfer_price > 0
GROUP BY contract_no
) t
GROUP BY t.所属人
HAVING COUNT(t.所属人) > 1
)
AND contract_no LIKE '%Q%'
AND xxf.transfer_price > 0
GROUP BY contract_no, suser.realName
) d
GROUP BY d.contract_no, 所属人
ORDER BY 所属人;