SELECT agvo1.org_id org_ids, ptd.deadline,t.*, (SELECT JSON_ARRAYAGG(JSON_OBJECT('annualGoalValueId', atv.id,'goalValue', atv.goal_value,'orderNo', atv.order_no,'attachments', atv.attachments,'orgs', (SELECT JSON_ARRAYAGG(JSON_OBJECT('orgId', org.id,'orgName', org.name))FROM orgINNER JOIN annual_goal_value_org atvo2 ON org.id = atvo2.org_idAND atvo2.org_id = agvo1.org_id -- *这里报错！WHERE atvo2.annual_goal_value_id = atv.id)))FROM annual_goal_value atvWHERE atv.target_id = t.idORDER BY order_no) AS annual_goal_values_json, pto.name office_name, ts.scope_name scope_nameFROM annual_goal_value_org agvo1LEFT JOIN annual_goal_value agv ON agvo1.annual_goal_value_id = agv.idJOIN target t ON agv.target_id = t.id AND t.deleted_at IS NULLAND t.status = 1 AND t.submit_type = 1LEFT JOIN user u ON t.created_by = u.idLEFT JOIN target_scope ts ON t.target_scope_id = ts.idLEFT JOIN pt_office pto ON t.office_id = pto.idJOIN pt_task_deadline ptd ON ptd.org_id = agvo1.org_id AND ptd.pt_task_id = (SELECT idFROM pt_taskWHERE FIND_IN_SET(agvo1.org_id, org_ids))WHERE t.year = ?
ORDER BY t.created_at DESC;
标注的地方报错，> 1054 - Unknown column 'agvo1.org_id' in 'on clause' ，我觉得难道是这里不能用外查询？就是子查询借用主查询中遍历的字段值。然后测试站没有报错，很奇怪，一样的SQL，一个报错一个不报错，唯一的变量就是MySQL的版本了，测试站是8.0.28，正式站是8.0.16，应该是版本问题。但是有个问题困扰着我，就是外查询这么通用的功能，怎么可能在这么小的版本里面进行更新修复或者开发出来，而且当我把这行注释掉，正式站又可以跑的通，可是我这个SQL又不止这个地方用到了外查询这个特性。太奇怪了。后面思考了一下，可能不是外查询的问题，我把过滤条件放在where语句就能跑的通，我很兴奋，原来是外查询放在join中是跑不通的。原本想就此结束，但是打这篇记录的时候发现，在后面的地方在join中也用到了外查询过滤，没有报错。所以问题到底是啥呢。
后续改成这个了：
SELECT agvo1.org_id org_ids, ptd.deadline, t.*, (SELECT JSON_ARRAYAGG(JSON_OBJECT('annualGoalValueId', atv.id,'goalValue', atv.goal_value,'orderNo', atv.order_no,'attachments', atv.attachments,'orgs', (SELECT JSON_ARRAYAGG(JSON_OBJECT('orgId', org.id,'orgName', org.name))FROM orgleft JOIN annual_goal_value_org atvo2 ON org.id = atvo2.org_idWHERE atvo2.annual_goal_value_id = atv.idAND atvo2.org_id = agvo1.org_id -- 放入到where过滤)))FROM annual_goal_value atv join shmec.annual_goal_value_org agvo3 ON atv.id = agvo3.annual_goal_value_idWHERE atv.target_id = t.idAND agvo3.org_id = agvo1.org_id -- 根据业务，增加了一个过滤ORDER BY order_no) AS annual_goal_values_json -- 某高校在某任务下被分配的年度目标值, pto.name office_name, ts.scope_name scope_nameFROM annual_goal_value_org agvo1LEFT JOIN annual_goal_value agv ON agvo1.annual_goal_value_id = agv.idJOIN target t ON agv.target_id = t.id AND t.deleted_at IS NULLAND t.status = 1 AND t.submit_type = 1LEFT JOIN user u ON t.created_by = u.idLEFT JOIN target_scope ts ON t.target_scope_id = ts.idLEFT JOIN pt_office pto ON t.office_id = pto.idJOIN pt_task_deadline ptd ON ptd.org_id = agvo1.org_id AND ptd.pt_task_id = (SELECT idFROM pt_taskWHERE FIND_IN_SET(agvo1.org_id, org_ids))WHERE t.year = 2024ORDER BY t.created_at DESC;
