选择订购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和2014-3-09内订购相同商品2次或2次以上的客户，不包括在2014-3-10 to 2014-3-14一周内购买相同商品的任何客户。

例如

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

以及预期产出的日期应该是

custid  | item  | item_count
22      | bread | 2

垫子11不符合清单，因为他们也是在2014-3-10 to 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项。

Answer 1

典型的MySQL误差你错误地用invid来代替垫子。

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;

编辑:好的，这是一个更近的观察它。

• 如前面所提到的，纠正组中的错误。
• 您的外部加入发票，虽然不应该有详细记录，没有发票记录。将此更改为内部连接。
• 你真是让人费解。购买日期应在“2014-2-10”至“2014-3-09”之间，不得介于“2014-3-10”至“2014-3-14”之间。
• 然后:你不想排除那些在后一周买东西的顾客。您想要排除当时发生的客户项目组合。

我的建议是:从两个日期范围中选择，然后检查是否所有客户商品组合的数据都在所需的周内，并且至少有两个：

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;

Answer 2

SELECT    i1.custid, d1.ITEM, COUNT(*) orders
FROM      (invoices i1 JOIN details d1 USING (invid))
LEFT JOIN (invoices i2 JOIN details d2 USING (invid))
       ON i2.custid = i1.custid
      AND d2.ITEM   = d1.ITEM
      AND i2.invdate BETWEEN '2014-03-10' AND '2014-03-14'
WHERE     i1.invdate BETWEEN '2014-02-10' AND '2014-03-09'
      AND i2.custid IS NULL
GROUP BY  i1.custid, d1.ITEM
HAVING    orders >= 2

请在木琴上看到。