mysql - 使用别名 MYSQL 的 CLAUSE 中出现未知列错误

Question

我有这套SQL命令

SELECT CONCAT (
        REPEAT('    ', (COUNT(parent.itemName) - 1)),
        node.itemName
        ) AS 'NAME_',
    node.itemNo AS 'ITEM_CODE',
    node.assemblyNo AS 'ASSEMBLY_NO.',
    ref_item_type.itemTypeName AS 'TYPE_',
    node.description AS 'DESCRIPTION_',
    node.serialNo AS 'SERIAL_NO',
    node.hardwareNo AS 'HARDWARE_NO',
    node.modelNo AS 'MODEL_NO',
    node.version AS 'VERSION_',
    ref_item_uom.uomName AS 'UNIT_',
    ref_item_cat.catName AS 'CATEGORY_',
    ref_item_product_type.prodTypeName AS 'PRODUCT TYPE',
    man_network.networkName AS 'NETWORK_',
    ref_item_package.packageName AS 'PACKAGE_',
    node.reorderPoint AS 'REORDER_POINT',
    node.cost AS 'COST_',
    node.price AS 'PRICE_',
    node.surcharge AS 'SURECHARGE_',
    node.validity AS 'VALIDITY_',
    node.validityUnit AS 'VALIDITY_UNIT',
    node.isSpecialItem AS 'SPECIAL_',
    node.isActive AS 'IS_ACTIVE_',
    node.itemIndex AS 'ITEM_INDEX'
FROM man_item AS node,
    man_item AS parent
LEFT JOIN ref_item_type ON ref_item_type.itemTypeNo = node.itemType
LEFT JOIN ref_item_uom ON ref_item_uom.uomNo = node.uomNo
LEFT JOIN ref_item_cat ON ref_item_cat.catNo = node.itemCat
LEFT JOIN ref_item_product_type ON ref_item_product_type.prodTypeNo = node.prodTypeNo
LEFT JOIN man_network ON man_network.networkNo = node.networkNo
LEFT JOIN ref_item_package ON ref_item_package.packageNo = node.packageNo
WHERE node.lft BETWEEN parent.lft
        AND parent.rgt
    AND node.isVisible = 'Y'

我遇到了一些错误

1054 - Unknown column 'node.itemType' in 'on clause'

我错过了什么吗？

PS:我忘记提及我的表格中的字段这是顺便说一句

itemIndex   
itemNo
subItem 
assemblyNo
refNo  
prodTypeNo 
networkNo
packageNo
itemName
itemType
itemSubTypeNo
itemCat
uomNo
serialNo
hardwareNo
modelNo
version
cost
price
surcharge
validity
validityUnit
logoLocation
fileHash
lft
rgt
isSpecialItem
isActive

我没有早点发布表格结构，这让我变得更加困惑和模糊

Answer 1

您可以尝试将这部分代码更改为正确的连接:

FROM man_item AS 节点，
man_item 作为父项
LEFT JOIN ref_item_type ON ref_item_type.itemTypeNo = node.itemType
引擎可能不知道如何读取“，”分隔的表列表，然后读取左连接并抛出随机错误。

FROM man_item AS 节点
左外连接 man_item 作为父项
ON node.lft 位于parent.lft 和parent.rgt 之间
AND 节点.isVisible = 'Y'
左连接 ref_item_type
ON ref_item_type.itemTypeNo = 节点.itemType