MySQL 获得的日期范围不正确结果 - 查询忽略指定范围

Question

我有一个查询涉及搜索 30 天范围内的数据库。正确输出和错误输出的查询如下：

CORRECT RESULTS:
SELECT
    affiliates.member_id,
    IFNULL( COUNT(orders.deal_id) , 0 ) AS deals_count,
    IFNULL( SUM(orders.quantity) , 0 ) AS deals_quanity
FROM affiliates
LEFT JOIN deals ON affiliates.member_id = deals.member_id
LEFT JOIN orders ON deals.deal_id = orders.deal_id
LEFT JOIN customers_orders_link ON orders.order_id = customers_orders_link.order_id
    AND DATE(customers_orders_link.datetime) BETWEEN '2011-06-01' AND '2011-07-01'
    AND customers_orders_link.order_status = 'Delivered'
GROUP BY affiliates.member_id;

EXPECTED &已收到：（正确）

MemberID    COUNT   SUM
1           11      16
2           0       0

错误结果：

//Notice the change in the date range

SELECT
    affiliates.member_id,
    IFNULL( COUNT(orders.deal_id) , 0 ) AS deals_count,
    IFNULL( SUM(orders.quantity) , 0 ) AS deals_quanity
FROM affiliates
LEFT JOIN deals ON affiliates.member_id = deals.member_id
LEFT JOIN orders ON deals.deal_id = orders.deal_id
LEFT JOIN customers_orders_link ON orders.order_id = customers_orders_link.order_id
    AND DATE(customers_orders_link.datetime) BETWEEN '2011-10-01' AND '2011-10-31'
    AND customers_orders_link.order_status = 'Delivered'
GROUP BY affiliates.member_id

预期：

MemberID    COUNT   SUM
1           0       0
2           0       0

但我收到：（不正确的输出）

MemberID    COUNT   SUM
1           11      16
2           0       0

第一个查询生成正确的结果，而第二个查询生成错误的结果。即使我使用过去的日期作为范围，我仍然收到相同的错误输出。就好像查询完全忽略了日期范围规范。所以这种忽略日期范围规范的情况似乎是问题所在。

如何使查询“查看”和“遵守”日期范围规范并实际收到上面列出的第二个查询的预期输出？

编辑1：

//Table: Orders
order_id        deal_id quantity        price
1               1       2               40.00
1               2       1               15.00
2               1       1               20.00
3               9       1               5.00
4               1       2               40.00
4               9       2               10.00
5               1       1               20.00
5               9       1               5.00
6               1       2               40.00
6               9       2               10.00
7               1       1               20.00
8               11      1               1.00


//Table: customers_orders_link
order_id        customer_id     order_status    datetime
1               4               Cancelled       2011-06-05 20:26:45
2               4               Delivered       2011-06-05 20:38:28
3               4               Pending Payment 2011-06-05 20:56:50
4               4               Pending Payment 2011-06-09 17:03:08
5               4               Pending Payment 2011-06-09 17:12:23
6               4               Pending Payment 2011-06-09 17:19:57
7               4               Pending Payment 2011-06-09 17:40:59
8               4               Pending Payment 2011-06-10 03:55:17

Answer 1

我自己用完全不同的方法解决了这个问题。

Answer 2

我不知道您的数据是什么样的，但我怀疑您的 LEFT JOINcustomers_orders_link 是罪魁祸首。如果您只想在满足该表的条件时计算 COUNT() 和 SUM()，则应该使用标准 JOIN 来代替 LEFT JOIN。