执行会出现 ( 1054 - Unknown column 'ai.record_node_history' in 'field list'),
该字段再表里是有的,因为 groupBy 子查询导致的吗 ?
SELECT ai.form_id AS formId
FROM approval_info ai
LEFT JOIN approval_config ac
ON ai.form_id = ac.form_id
AND ai.process_progress = ac.approval_node
WHERE
ac.corp_id = 'dayReport'
AND ai.end_state = 0
AND ai.launch_time >= '2024-10-01'
AND ai.launch_time 0
AND NOT EXISTS (
SELECT 1
FROM approval_record ar
WHERE ar.approval_info_id = ai.approval_info_id
AND ar.approver_node != ai.process_progress
AND ar.accessory_url != ai.object_id
AND ar.approver_user_id = 'liuys'
AND ar.approver_count = (
SELECT COUNT(*)
FROM (
-- 计算 record_node_history 中 process_progress 出现的次数
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ai.record_node_history, ',', numbers.n), ',', -1) AS node
FROM (
SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10
) numbers
WHERE CHAR_LENGTH(ai.record_node_history) - CHAR_LENGTH(REPLACE(ai.record_node_history, ',', '')) + 1 >= numbers.n
) AS temp
-- 比较 record_node_history 中的节点与当前 process_progress
WHERE temp.node = CAST(ai.process_progress AS CHAR)
)
)
ORDER BY ai.form_id
LIMIT 10 OFFSET 0;