SQL 问题修改

[imyasON]

求大佬看下怎么改，ai 问不出来。
执行会出现 （ 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 <= '2024-10-31'
AND ai.process_progress != 0
AND FIND_IN_SET('liuys', ac.current_node_approver) > 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;

[mx3y]

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 <= '2024-10-31'
AND ai.process_progress != 0
AND FIND_IN_SET('liuys', ac.current_node_approver) > 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 (
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
WHERE temp.node = CAST(ai.process_progress AS CHAR)
)
)
ORDER BY ai.form_id
LIMIT 10 OFFSET 0;

[imyasON]

@mx3y #1 执行后和我一样的报错提示

[wengyanbin]

还是给下表结构还有部分数据吧，纯看着没什么问题。

[imyasON]

@wengyanbin #3 三张表贴到附言里了

[wengyanbin]

@imyasON 再给点 mock 的数据。没数据玩不转