SQL多日期查询

Question

我正在处理一个查询，其中我必须在具有多个日期范围的零件上查找3个或更多订单。我遇到的问题是，查询看起来并没有特别地查看日期范围…它查看的是完整的日期范围。

 SELECT partnumber, count(orders.partorderid) as numberoforders 
 FROM orders
 WHERE(orderdate between '2013-10-07' and '2013-10-13') or (orderdate between '2013-10-14' and '2013-10-20')
 GROUP BY partnumber
 HAVING COUNT(partorderid) > 2  

查询返回介于10/07-10/20之间的numberoforders，而不是10/07-10/13和10/14-10/20？

Answer 1

试试这个：

SELECT partnumber,
    SUM(CASE WHEN orderdate BETWEEN '2013-10-07' and '2013-10-13' THEN 1 END) week1,
    SUM(CASE WHEN orderdate BETWEEN '2013-10-14' and '2013-10-20' THEN 1 END) week2
FROM orders
GROUP BY partnumber
HAVING SUM(CASE WHEN orderdate BETWEEN '2013-10-07' and '2013-10-13' THEN 1 END) > 2
    AND SUM(CASE WHEN orderdate BETWEEN '2013-10-14' and '2013-10-20' THEN 1 END) > 2

或者你可能更喜欢：

SELECT *
FROM (
    SELECT partnumber,
        SUM(CASE WHEN orderdate BETWEEN '2013-10-07' and '2013-10-13' THEN 1 END) week1,
        SUM(CASE WHEN orderdate BETWEEN '2013-10-14' and '2013-10-20' THEN 1 END) week2
    FROM orders
    GROUP BY partnumber
) t WHERE week1 > 2 AND week2 > 2

这就减少了重复次数。

仅供参考，如果orderdate是约会对象，你就没问题。但如果它有日期和非零时间，您将丢失2013-10-13 00:00:00之后和2013-10-14 00:00:00之前的订单。