SELECT etc.order_id, JSON_UNQUOTE( JSON_EXTRACT( etc.parameters, '$.line_id' )) AS line_id, tt.company_id, tc.company_name FROM eb_travel_cart AS etc LEFT JOIN travelmanager_travelline AS tt ON line_id = tt.id LEFT JOIN travelmanager_company AS tc ON tt.company_id = tc.id WHERE etc.paid = 0

根据查询语句，错误出现在 LEFT JOIN travelmanager_travelline AS tt ON line_id = tt.id 这一行。这里的 line_id 是从 JSON 字符串中提取出来的，所以需要使用别名来引用。你需要将查询语句修改为：

SELECT etc.order_id, 
       JSON_UNQUOTE(JSON_EXTRACT(etc.parameters, '$.line_id')) AS line_id, 
       tt.company_id, 
       tc.company_name 
FROM eb_travel_cart AS etc 
LEFT JOIN travelmanager_travelline AS tt ON JSON_UNQUOTE(JSON_EXTRACT(etc.parameters, '$.line_id')) = tt.id 
LEFT JOIN travelmanager_company AS tc ON tt.company_id = tc.id 
WHERE etc.paid = 0

这样就可以解决 #1054 - Unknown column 'line_id' in 'on clause' 的问题了。

相关问题

简化sql: select JSON_UNQUOTE(JSON_EXTRACT(woo.attachment, '$.transformerObjList')) as transformerObjList from workflow_order woo where workflow_order_id in ( select c.workflow_order_id from ( select concat( JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.province')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.city')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.county')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.town')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.village')) ) as address , JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.dataType')) as dataType ,wo.workflow_order_id from workflow_order as wo ) as c where c.dataType = 1587434126787240000 and c.address = '江苏省无锡市宜兴市A镇8村'

简化后的 SQL 查询语句如下：

SELECT JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.transformerObjList')) AS transformerObjList
FROM workflow_order
WHERE workflow_order_id IN (
    SELECT workflow_order_id
    FROM (
        SELECT CONCAT(
            JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.province')),
            JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.city')),
            JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.county')),
            JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.town')),
            JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.village'))
        ) AS address,
        JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.dataType')) AS dataType,
        workflow_order_id
        FROM workflow_order
    ) AS c
    WHERE c.dataType = 1587434126787240000 AND c.address = '江苏省无锡市宜兴市A镇8村'
)

简化sql: select cc.villageMaxDocumentCnt from ( select concat ( JSON_UNQUOTE(JSON_EXTRACT(wo.attachment,'$.province')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment,'$.city')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment,'$.county')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment,'$.town')), JSON_UNQUOTE(JSON_EXTRACT(wo.attachment,'$.village')) ) as address , JSON_UNQUOTE(JSON_EXTRACT(wo.attachment, '$.villageMaxDocumentCnt')) as villageMaxDocumentCnt from workflow_order as wo where name = '地区审批单' ) cc where cc.address = '黑龙江省哈尔滨市阿城区舍利镇丰收村' and cc.villageMaxDocumentCnt is not null ;

简化后的 SQL 查询语句如下：

SELECT cc.villageMaxDocumentCnt
FROM (
    SELECT CONCAT(
        JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.province')),
        JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.city')),
        JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.county')),
        JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.town')),
        JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.village'))
    ) AS address,
    JSON_UNQUOTE(JSON_EXTRACT(attachment, '$.villageMaxDocumentCnt')) AS villageMaxDocumentCnt
    FROM workflow_order
    WHERE name = '地区审批单'
) cc
WHERE cc.address = '黑龙江省哈尔滨市阿城区舍利镇丰收村' AND cc.villageMaxDocumentCnt IS NOT NULL;