mysql - 没有为派生表中的列号指定列名

Question

我收到此错误消息，有人可以建议解决方案吗？

我浏览了这个论坛，找不到解决方案

Dynamic SQL Error
SQL error code = -104
Invalid command
no column name specified for column number 1 in derived table ACCRG

SELECT DISTINCT locationgroup.name AS MainGroup, product.num AS ProductNumber,
    product.description AS ProductDescription, uom.code AS UOM, company.name AS company,
    sum(((SELECT SUM(productsSold.qtyfulfilled) FROM soitem productsSold WHERE soitem.id = productsSold.id AND productsSold.productid = product.id) * COALESCE(uomconversion.multiply,1)) / COALESCE(uomconversion.factor,1)) AS uomqty

FROM soitem
    LEFT OUTER JOIN so ON (so.id = soitem.soid)
    LEFT OUTER JOIN product ON (soitem.productid = product.id)
    LEFT OUTER JOIN uom ON (product.uomid = uom.id)
    LEFT OUTER JOIN locationgroup ON (so.locationgroupid = locationgroup.id)
    LEFT OUTER JOIN qbclass ON (qbclass.id = soitem.qbclassid)
    LEFT OUTER JOIN customer ON (customer.id = so.customerid)
    LEFT JOIN (SELECT max(accountgrouprelation.id) AS relationID, accountgrouprelation.accountid
                        FROM  accountgrouprelation
            LEFT JOIN accountGroup ON accountGroupRelation.groupId = accountGroup.id
           WHERE UPPER(COALESCE (accountgroup.name, '%')) LIKE UPPER('%')
                        GROUP BY 2) accrg ON customer.accountid = accrg.accountid
    LEFT JOIN accountgrouprelation ON accrg.relationid = accountgrouprelation.id
    LEFT OUTER JOIN accountgroup ON (accountgroup.id = accountgrouprelation.groupid)
    LEFT OUTER JOIN uomconversion ON ((product.uomid = uomconversion.touomid) AND (soitem.uomid = uomconversion.fromuomid))
    JOIN company ON company.id = 1

WHERE soitem.qtyfulfilled > 0
  AND soitem.typeid in (10,12,80)
  AND locationgroup.id IN (1,2,3,4)
  AND qbclass.id LIKE '%'
  AND UPPER(so.salesman) LIKE UPPER('%')
  AND UPPER(COALESCE (accountgroup.name, '%')) LIKE UPPER('%')
  AND soitem.datelastfulfillment BETWEEN '2015-11-30 23:59:59.0' AND '2015-12-31 23:59:59.0'
GROUP BY maingroup, productnumber, productdescription, uom, company
ORDER BY 1,product.num ASCENDING

Answer 1

信不信由你，我认为错误来自您的 ORDER BY 语句:

ORDER BY 1,product.num ASCENDING

我认为在 MySQL 中 ORDER BY 一个常量是不合法的。从错误中可以看出，MySQL 似乎正试图在 ACCRG 派生表中查找名为 1 的列。不存在这样的列，因此您会收到此错误。相反，为什么不只是 ORDER BY 产品编号？:

ORDER BY product.num ASCENDING