在 CSDN 上受人指点惹,这里把 SQL 贴出来,希望能帮到一些人
作者:鏡音連改二
链接:
https://www.zhihu.com/question/52243984/answer/129648873来源:知乎
著作权归作者所有,转载请联系作者获得授权。
SELECT
apply.org_name AS 分公司,
n.statusdes AS 实时状态,
operation_log.apply_id,
operation_log.operator_name AS 合同复核操作人,
max(
operation_log.operation_time
) AS 合同复核时间,
contract.contract_number AS 合同编号,
contract.actual_loan_time AS 实际放款时间,
contract.loan_amount AS 合同金额
FROM
operation_log
LEFT JOIN contract ON contract.apply_id = operation_log.apply_id
LEFT JOIN apply ON apply.apply_id = operation_log.apply_id
LEFT JOIN (
SELECT
group_concat(
DISTINCT c.status_code SEPARATOR '||'
) AS statussum,
c.is_in_node,
c.apply_id,
group_concat(
DISTINCT c.status_name SEPARATOR '||'
) AS statusdes,
group_concat(
DISTINCT c.operator_name SEPARATOR '||'
) AS operatornames
FROM
node_record c
WHERE
c.is_in_node = 1
AND c.is_valid = 1
GROUP BY
c.apply_id
) n ON apply.apply_id = n.apply_id
WHERE
operation_time >= "2016-11-1"
AND before_status_name = "合同复核中"
AND end_status_name = "合同复核完成"
AND operation_log.node_code = "REVIEW"
AND apply.is_ex_apply = '1'
AND apply.borrowing_type = '1'
AND operation_log.operator_name NOT LIKE "%测试%"
GROUP BY
apply.org_name,
n.statusdes,
operation_log.apply_id,
operation_log.operator_name,
DATE(
operation_log.operation_time
),
contract.contract_number,
contract.actual_loan_time,
contract.loan_amount