mysql - MySQL总和的某种联系

查询中的Sum函数之前是否存在某种协商的可能性？我有以下查询：

SELECT
  orders.id AS orderId,
  orders.delivery_from,
  orders.delivery_to,
  orders_products.product_id,
  orders_products.color_id,
  orders_products.size_id,
  SUM(orders_products.quantity) AS quantity,
  customers.id AS customerId,
  customers.name AS customerName,
  products.name
FROM
  orders
  INNER JOIN orders_products
    ON orders_products.order_id = orders.id
  INNER JOIN customers
    ON customers.id = orders.customer_id
  INNER JOIN products
    ON orders_products.product_id = products.id
  LEFT JOIN orders_product_data
    ON orders_product_data.order_id = orders.id
    AND orders_product_data.product_id = orders_product.product_id
    AND orders_product_data.color_id = orders_product.color_id
WHERE orders.status = '0'
  AND (
    orders.delivery_from <= '2014-05-05'
    AND orders.delivery_to >= '2014-05-05'
  )
  AND (
    (orders_products_data.delivery_from <= '2014-05-05' || orders_products_data.delivery_to = 0)
    AND (orders_products_data.delivery_from >= '2014-05-05' || orders_products_data.delivery_to = 0)
  )
GROUP BY customer_id,
  product_id,
  color_id,
  size_id

我想为每个客户订购的产品数量。当然，我可以使用内部联接进行计算，但是有更好的方法吗？

最佳答案

从注释开始，您可以使用subselect进行附加联接，这将是内部联接，并且仅会获得产品数量超过400的客户

SELECT
  GROUP_CONCAT(DISTINCT CONVERT(o.id, CHAR(11))) orders,
  o.delivery_from,
  o.delivery_to,
  o.customer_id,
  op.product_id,
  op.color_id,
  op.size_id,
  sum(op.quantity) as quantity
FROM orders o
  INNER JOIN orders_products op  ON op.order_id=o.id
  LEFT JOIN orders_products_data opd ON opd.order_id=o.id
AND opd.product_id=op.product_id
AND opd.color_id=op.color_id
JOIN (SELECT customer_id FROM orders o
      JOIN orders_products op ON(op.order_id=o.id)
     GROUP BY customer_id
      HAVING SUM(op.quantity) >400
     ) t
ON(o.customer_id = t.customer_id)
WHERE
  o.status='0' AND
  (o.delivery_from <= '2014-05-05'
   AND o.delivery_to >='2014-05-05')
   AND
  ((opd.delivery_from<='2014-05-05'
    || opd.delivery_to=0)
     AND (opd.delivery_from>='2014-05-05' ||
          opd.delivery_to=0))
GROUP BY o.customer_id ,
op.product_id,
op.color_id,
op.size_id

对于上述查询，您应该索引ON()子句中使用的列，还应该索引where过滤器中涉及的日期列

Demo