Mysql count() 获取错误结果

Question

我正在尝试查找本周的记录数。我当前的查询是:

SELECT Week(Str_to_date(products_options_values, '%m-%d-%Y'), 1) AS order_week,
       Year(Str_to_date(products_options_values, '%m-%d-%Y')) AS order_year,
       order_active,
       Count(op.sub_order_id) AS deliveries
FROM   orders_products_attributes opa
       LEFT JOIN orders_products op
              ON ( opa.orders_products_id = op.orders_products_id )
GROUP  BY order_week,
          order_year
HAVING order_week = '31'
       AND order_year >= '2013'
       AND order_active = 0
ORDER  BY order_week

它获取 deliveries AS 2，因为实际上有 4 条记录，如果我在删除 COUNT 和 后运行相同的查询>GROUP BY，它正确显示了所有 4 行。同样的问题也发生在其他周，例如第 34 周有 3 条记录，但上面的查询将其取为 4 条。此外，另一个奇怪的事情是，在 GROUP BY 子句中，如果我删除 order_week 或 order_year 中的任何一个，查询将返回一个空结果集.

知道我做错了什么吗？

Answer 1

尝试将所有 HAVING 条件移动到 WHERE。此外，Count(id) - 计算 ID 的唯一值，而不是全部。如果您需要所有记录计数，只需使用 COUNT(*)

SELECT Week(Str_to_date(products_options_values, '%m-%d-%Y'), 1) AS order_week,
       Year(Str_to_date(products_options_values, '%m-%d-%Y')) AS order_year,
       order_active,
       Count(op.sub_order_id) AS deliveries
FROM   orders_products_attributes opa
       LEFT JOIN orders_products op
              ON ( opa.orders_products_id = op.orders_products_id )
WHERE order_week = '31'
       AND order_year >= '2013'
       AND order_active = 0

GROUP  BY order_week,
          order_year
ORDER  BY order_week