SQL 问题修改

V2EX = way to explore

V2EX 是一个关于分享和探索的地方

现在注册

已注册用户请登录

这是一个创建于 57 天前的主题，其中的信息可能已经有所发展或是发生改变。

求大佬看下怎么改，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;

第 1 条附言 · 56 天前

1. `approval_info` 表

CREATE TABLE IF NOT EXISTS approval_info (
    approval_info_id         INT AUTO_INCREMENT 
    corp_id                  VARCHAR(50),
    approval_config_group_id VARCHAR(255) ,
    form_id                  VARCHAR(50)   ,
    founder                  VARCHAR(255)   , 
    launch_time              DATETIME  ,    
    end_state                TINYINT(1) ,
    end_time                 DATETIME  ,
    object_id                VARCHAR(255)  ,
    process_progress         INT  ,
    record_node_history      VARCHAR(255) ,
) COMMENT '审批流程发起表';

CREATE TABLE IF NOT EXISTS approval_config (
    approval_config_id          INT AUTO_INCREMENT,
    corp_id                     VARCHAR(100),
    node_name                   VARCHAR(100),
    form_id                     VARCHAR(50), 
    approval_auto_pass          TINYINT(1),
    current_node_approver_type  TINYINT,      
    current_node_approver       VARCHAR(255) , 
    care_of                     TINYINT(1) ,  
    approval_type               TINYINT,          
    approval_node               INT  ,             
    carbon_copy_recipients_type TINYINT  ,
    carbon_copy_recipients      VARCHAR(255) ,
    create_time                 DATETIME     ,       
    group_id                    VARCHAR(255),      
) COMMENT '审批流程节点规则配置表';

CREATE TABLE IF NOT EXISTS approval_record (
    approval_record_id INT AUTO_INCREMENT,
    corp_id            VARCHAR(50) ,
    approval_info_id   VARCHAR(50) ,  
    form_id            VARCHAR(50) ,  
    approver_user_id   VARCHAR(50) ,    
    approver_time      DATETIME,        
    approver_opinions  VARCHAR(255) ,   
    approver_result    TINYINT,   
    transferee         VARCHAR(50),   
    transferee_text    VARCHAR(255) ,
    accessory_url      VARCHAR(255),   
    approver_node      TINYINT,   
    approver_count     TINYINT DEFAULT 1 NULL COMMENT ',
    approver_username  VARCHAR(30) ,
) COMMENT '审批记录表';

5 条回复 • 2024-10-30 21:40:29 +08:00

mx3y

57 天前

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

57 天前

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

wengyanbin

57 天前

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

imyasON

56 天前

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

wengyanbin

56 天前

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

SQL 问题修改

1. approval_info 表

1. `approval_info` 表