将正常运行的SQL查询改写为SQLite兼容语法（ON子句报错修复）
2026-6-17
SQLite中ON子句报错的修正方案
问题背景
现有一段可正常运行的SQL查询语句，但在SQLite环境执行时触发ON子句相关报错，经定位，报错由以下代码段导致：
LEFT OUTER JOIN tew_componentterminal ON vew_wire_ex.wir_cel_idfrom = tew_componentterminal.cte_cel_id AND vew_wire_ex.wir_cte_nofrom = tew_componentterminal.cte_no OR vew_wire_ex.wir_cel_idto = tew_componentterminal.cte_cel_id AND vew_wire_ex.wir_cte_noto = tew_componentterminal.cte_no ON tew_componentelement.cel_id = tew_componentterminal.cte_cel_id
错误根源
- JOIN语法不规范：SQLite要求每个
JOIN必须紧跟对应的ON关联条件，原代码中LEFT OUTER JOIN vew_wire_ex未指定关联条件就直接嵌套后续JOIN，且出现连续两个ON子句，违反SQLite语法规则。 - 逻辑优先级错误：
OR运算符优先级低于AND，原代码中的条件会被错误解析，导致关联逻辑偏离预期。
修正后的关键代码段
将嵌套JOIN结构调整为明确的子查询块，用括号包裹OR两侧的逻辑组，确保每个JOIN都有对应的关联条件：
LEFT OUTER JOIN ( vew_wire_ex LEFT OUTER JOIN tew_componentterminal ON ( (vew_wire_ex.wir_cel_idfrom = tew_componentterminal.cte_cel_id AND vew_wire_ex.wir_cte_nofrom = tew_componentterminal.cte_no) OR (vew_wire_ex.wir_cel_idto = tew_componentterminal.cte_cel_id AND vew_wire_ex.wir_cte_noto = tew_componentterminal.cte_no) ) ) ON tew_componentelement.cel_id = tew_componentterminal.cte_cel_id
完整修正后的SQL语句
SELECT DISTINCT tew_componentparent.com_tagpath AS vcomcom_com_tagpath , tew_component.com_tagpath AS com_tagpath , tew_component.com_id AS com_id , tew_component.com_tagmanual AS com_tagmanual , tew_componentelement.cel_group AS cel_group , tew_componentelement.cel_orderno AS cel_orderno , tew_componentelement.cel_id AS cel_id , tew_componentterminal.cte_txt AS cte_txt , tew_componentterminal.cte_no AS cte_no , vew_wire_ex.cab_id AS cab_id , vew_wire_ex.cab_tag AS cab_tag , vew_wire_ex.caw_no AS caw_no , vew_wire_ex.caw_section AS caw_section , vew_wire_ex.caw_tra_0_l1 AS caw_tra_0_l1 , vew_wire_ex.wir_tag AS wir_tag , vew_wire_ex.wir_id AS wir_id , vew_wire_ex.vwircomto_com_id AS vwircomto_com_id , vew_wire_ex.wir_type AS wir_type , vew_wire_ex.equ_text AS equ_text , vew_wire_ex.equ_id AS equ_id , vew_wire_ex.equ_columnmark AS equ_columnmark , vew_wire_ex.equ_rowmark AS equ_rowmark , vew_wire_ex.cab_ductpath AS cab_ductpath , vew_wire_ex.vwircomcomfrom_com_tagpath AS vwircomcomfrom_com_tagpath , vew_wire_ex.vwircomcomto_com_tagpath AS vwircomcomto_com_tagpath , vew_wire_ex.vwircomfrom_com_tagpath AS vwircomfrom_com_tagpath , vew_wire_ex.vwircomto_com_tagpath AS vwircomto_com_tagpath , vew_wire_ex.vwircomfrom_com_tagmanual AS vwircomfrom_com_tagmanual , vew_wire_ex.vwircomto_com_tagmanual AS vwircomto_com_tagmanual , vew_wire_ex.vwircelfrom_cel_group AS vwircelfrom_cel_group , vew_wire_ex.vwircelto_cel_group AS vwircelto_cel_group , vew_wire_ex.vwirctefrom_cte_txt AS vwirctefrom_cte_txt , vew_wire_ex.vwircteto_cte_txt AS vwircteto_cte_txt , locfrom.loc_tagpath AS locfrom_tagpath , locto.loc_tagpath AS locto_tagpath , funfrom.fun_tagpath AS funfrom_tagpath , funto.fun_tagpath AS funto_tagpath , funfrom.fun_tra_0_l1 AS funfrom_tra_0_l1 , funto.fun_tra_0_l1 AS funto_tra_0_l1 , locfrom.loc_tra_0_l1 AS locfrom_tra_0_l1 , locto.loc_tra_0_l1 AS locto_tra_0_l1 , locto.loc_id AS loc_to_id , CASE WHEN vwircomto_com_id = tew_component.com_id THEN vwircomcomfrom_com_tagpath ELSE vwircomcomto_com_tagpath END AS comdest_comcom_tagpath , CASE WHEN vwircomto_com_id = tew_component.com_id THEN vwircomfrom_com_tagpath ELSE vwircomto_com_tagpath END AS comdest_com_tagpath , CASE WHEN vwircomto_com_id = tew_component.com_id THEN vwircomfrom_com_tagmanual ELSE vwircomto_com_tagmanual END AS comdest_com_tagmanual , CASE WHEN vwircelto_cel_id = tew_componentelement.cel_id THEN vwircelfrom_cel_group ELSE vwircelto_cel_group END AS comdest_cel_group , CASE WHEN vwircelto_cel_id = tew_componentelement.cel_id THEN vwirctefrom_cte_txt ELSE vwircteto_cte_txt END AS comdest_cte_txt , CASE WHEN vwircelto_cel_id = tew_componentelement.cel_id THEN locfrom.loc_tagpath ELSE locto.loc_tagpath END AS comdest_loc_tagpath , CASE WHEN vwircelto_cel_id = tew_componentelement.cel_id THEN funfrom.fun_tagpath ELSE funto.fun_tagpath END AS comdest_fun_tagpath , CASE WHEN vwircelto_cel_id = tew_componentelement.cel_id THEN funfrom.fun_tra_0_l1 ELSE funto.fun_tra_0_l1 END AS comdest_fun_tra_0_l1 , CASE WHEN vwircelto_cel_id = tew_componentelement.cel_id THEN locfrom.loc_tra_0_l1 ELSE locto.loc_tra_0_l1 END AS comdest_loc_tra_0_l1 , vew_wire_ex.cab_standard AS cab_standard , vew_wire_ex.wir_sectionorgauge AS wir_sectionorgauge , vew_wire_ex.wir_sectiontype AS wir_sectiontype , vew_plcinout_ex.cel_id AS GtW_CELID , vew_plcinout_ex.aio_tra_0_l1 , vew_plcinout_ex.aio_tra_1_l1 , vew_plcinout_ex.aio_tra_2_l1 , vew_plcinout_ex.aio_tra_3_l1 , vew_plcinout_ex.aio_tra_4_l1 , vew_plcinout_ex.aio_mnemonic , vew_plcinout_ex.aio_id , vew_plcinout_ex.aio_cel_id , vew_plcinout_ex.bom_reference AS bom_reference FROM vew_plcinout_ex INNER JOIN tew_componentelement ON vew_plcinout_ex.cel_id = tew_componentelement.cel_id LEFT OUTER JOIN ( vew_wire_ex LEFT OUTER JOIN tew_componentterminal ON ( vew_wire_ex.wir_cel_idfrom = tew_componentterminal.cte_cel_id AND vew_wire_ex.wir_cte_nofrom = tew_componentterminal.cte_no OR vew_wire_ex.wir_cel_idto = tew_componentterminal.cte_cel_id AND vew_wire_ex.wir_cte_noto = tew_componentterminal.cte_no ) ) ON tew_componentelement.cel_id = tew_componentterminal.cte_cel_id LEFT OUTER JOIN tew_component ON tew_component.com_id = tew_componentelement.cel_com_id LEFT OUTER JOIN tew_component AS tew_componentparent ON tew_componentparent.com_id = tew_component.com_com_id LEFT OUTER JOIN vew_location AS locfrom ON vew_wire_ex.vwircomfrom_com_loc_id = locfrom.loc_id LEFT OUTER JOIN vew_location AS locto ON vew_wire_ex.vwircomto_com_loc_id = locto.loc_id LEFT OUTER JOIN vew_function AS funfrom ON vew_wire_ex.vwircomfrom_com_fun_id = funfrom.fun_id LEFT OUTER JOIN vew_function AS funto ON vew_wire_ex.vwircomto_com_fun_id = funto.fun_id WHERE (vew_plcinout_ex.aio_cel_id > - 1)
内容的提问来源于stack exchange，提问作者GaituutH
相关产品推荐
相关产品推荐
