mysql - 选择 n 次订购的特定项目

Question

我有两个表，一个是发票，另一个是我需要选择特定客户在一个日期范围内订购 n 次的产品的详细信息

部分表格如下所示

发票

invid | custid | invdate
----------------------------
101   | 11     | 2014-2-10
102   | 22     | 2014-2-15
103   | 22     | 2014-3-01
104   | 11     | 2014-3-14

详情

invid | item     
------------
101   | bread   
102   | bread  
103   | chips
104   | chips
102   | bread   
103   | bread  
104   | chips
101   | bread

从上面的代码中，我需要选择所有在 2014-2-10 内订购相同商品 2 次或更多次的客户。和 2014-3-09 , 不包括在一周内购买相同商品的任何客户 2014-3-10至 2014-3-14

例如

如果客户下单bread date1 和 date2 之间有 2 次，并且没有订购相同的 bread在 date3 和 date4 之间，那么它应该在输出中

和预期输出的日期

custid  | item  | item_count
22      | bread | 2

custid 11 不适合列表，因为他们也在这周购买了 2014-3-10至 2014-3-14 , 但如果他们没有在过去的日期内购买相同的商品

这是我试过的

SELECT 
i.custid, d.ITEM,COUNT(d.ITEM) as orders
From `details` d 
LEFT JOIN `invoices` i on i.invid= d.invid 
WHERE 
i.invdate >= '2014-2-10' AND 
i.invdate <= '2014-3-14' AND 
i.custid NOT IN 
(SELECT custid FROM `invoices` WHERE invdate >= '2014-3-10') 
Group By i.invid, d.ITEM 
HAVING COUNT(d.ITEM) >= 2

当我再次运行整个表时，我得到 1 个项目而不是 6 个。我确实通过许多函数手动使用 excel 来确定，在这种情况下没有

Answer 1

典型的 MySQL 错误。您错误地按 invid 而不是 custid 分组。

SELECT 
i.custid, d.ITEM, COUNT(d.ITEM) as orders
From `details` d 
LEFT JOIN `invoices` i on i.invid= d.invid 
WHERE 
i.invdate >= '2014-2-10' AND 
i.invdate <= '2014-3-14' AND 
i.custid NOT IN 
(SELECT custid FROM `invoices` WHERE invdate >= '2014-3-10') 
Group By i.custid, d.ITEM 
HAVING COUNT(d.ITEM) >= 2;

编辑:好的，这里仔细看看它。

• 如前所述更正 GROUP BY。
• 尽管没有发票记录就不应该有详细记录，但您可以外部加入发票。将其更改为 INNER JOIN。
• 你混淆了日期。购买日期应介于“2014-2-10”和“2014-3-09”之间，不得介于“2014-3-10”和“2014-3-14”之间
• 然后:您不想排除在后一周内购买过商品的客户。您想要排除当时发生的客户-项目组合。

我的建议:从两个日期范围中选择并检查客户项目组合的所有 macthes 是否都在所需的一周内并且仍然有至少两个计数:

select 
  i.custid, 
  d.item,
  count(d.item) as orders
from invoices i
inner join details d on d.invid = i.invid
where i.invdate between '2014-2-10' and '2014-3-09' 
   or i.invdate between '2014-3-10' and '2014-3-14'
group by i.custid, d.item 
having count(*) >= 2 and max(i.invdate) between '2014-2-10' and '2014-3-09;