MySQL - 将 ID 匹配到 Max of Count ("Need"更优雅的解决方案)

Question

是否有更好的方法来执行以下操作:

SELECT ProductID, MAX(a.countProductID)
FROM
    (
    SELECT ProductID, COUNT(ProductID) as countProductID
    FROM    SalesOrderDetail
            LEFT JOIN Product USING (ProductID)
    GROUP BY ProductID
    ) as a
WHERE a.countProductID =    (SELECT MAX(x.countProductID) FROM
                            (
                                SELECT ProductID, COUNT(ProductID) as countProductID
                                FROM    SalesOrderDetail
                                        LEFT JOIN Product USING (ProductID)
                                GROUP BY ProductID
                            ) as x
                            );

因为我两次使用相同的子查询。但是我无法从 WHERE 子句访问第一个。

Answer 1

我猜任务是找到一个或多个销售量最大的产品。首先，您不应该加入 PRODUCT 表，因为您需要的所有信息都在 SalesOrderDetail 表中。然后使用 LIMIT 1 找到最大计数并使用 HAVING 选择所有具有最大计数的产品:

SELECT ProductID, COUNT(ProductID) as countProductID
FROM    SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(ProductID) = (SELECT COUNT(ProductID) as countProductID 
                             FROM SalesOrderDetail 
                           GROUP BY ProductID 
                           ORDER BY countProductID DESC
                           LIMIT 1 )