php - 我的本地计算机上的 MYsql 错误。但它在现场工作正常

Question

这是我的 MySQL 查询

SELECT sum(quantity) as quantity_sold, `gc_order_items`.`name` as `name`, `sku` FROM `gc_orders` JOIN `gc_order_items` ON `gc_order_items`.`order_id` = `gc_orders`.`id` WHERE `status` != 'cart' AND `gc_order_items`.`type` = 'product' GROUP BY `product_id`

我在本地服务器上收到此错误。

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'go.gc_order_items.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Please help!

Answer 1

这是您当前的查询:

SELECT
    SUM(quantity) AS quantity_sold,
    gc_order_items.name AS name,
    sku
FROM gc_orders
INNER JOIN gc_order_items
    ON gc_order_items.order_id = gc_orders.id
WHERE status != 'cart' AND
      gc_order_items.type = 'product'
GROUP BY product_id

在您的本地计算机上很可能是 ONLY_FULL_GROUP_BY模式已打开。在这种符合 ANSI SQL 标准的模式下，GROUP BY 聚合查询中的每一列必须出现在 GROUP BY 子句中或出现在聚合函数中，例如SUM()。在上述查询的情况下，您选择了 sku 和 name 列，但它们没有出现在 GROUP BY 中，也没有出现出现在聚合函数内部，因此出现错误。

在实时机器上，我猜测严格模式已关闭，这允许您的查询继续进行。

以下是对在任何地方都能正确运行的查询版本的猜测:

SELECT
    t2.quantity_sold,
    t2.name,
    t1.sku
FROM gc_orders t1
INNER JOIN
(
    SELECT order_id, name, SUM(quantity) AS quantity_sold
    FROM gc_order_items
    WHERE type = 'product'
    GROUP BY order_id, name
) t2
    ON t1.id = t2.order_id
WHERE t1.status != 'cart'