选择在特定时间段之前和之后存在的值 [英] Select values which exist before and after a certain time period

问题描述

我有以下简单表格，您也可以在SQL Fiddle 此处找到:

I have the following simple table which you can also find in the SQL Fiddle here:

CREATE TABLE Orders (
    Customer TEXT,
    Order_Date DATE

);

INSERT INTO Orders
(Customer, Order_Date)
VALUES 
("Customer A", "2017-05-23"),
("Customer B", "2018-09-10"),
("Customer B", "2018-12-14"),
("Customer A", "2019-01-03"),
("Customer A", "2019-02-15"),
("Customer C", "2017-09-04"),
("Customer B", "2019-01-09"),
("Customer A", "2019-02-16"),
("Customer C", "2019-02-12"),
("Customer B", "2018-01-03");

您可以看到该表显示了来自不同客户的订单日期.
我使用下面的SQL获取所有在2019年2月下订单的客户:

As you can see the table displays the order dates from different customers.
I use the below SQL to get the all customers that placed an order in February 2019:

SELECT Customer
FROM ORDERS
WHERE Order_Date >= "2019-02-01 00:00:00"
AND Order_Date < "2019-03-01 00:00:00"

到目前为止，所有这些都工作正常.

All this works fine so far.

但是，现在我要实现的是，仅列出列出哪些客户

However, now I want to achieve that only customers are listed which

a)在2月 和

WHERE order in >= "2019-02-01 00:00:00" AND < "2018-03-01 00:00:00"

b)没有在 12个月前和

WHERE no order < "2019-02-01 00:00:00" AND >= "2018-01-02 00:00:00"

c)在此12个月内下订单

c) placed an order before this 12 month period

WHERE order in < "2018-01-02 00:00:00"

要实现此目的，我需要更改查询中的什么内容?

What do I need to change in my query to achieve this?

推荐答案

这有点复杂，但是您可以使用group by和having:

That is a bit complicated, but you can use group by and having:

SELECT o.Customer
FROM ORDERS o
GROUP BY o.Customer
HAVING SUM( o.Order_Date >= '2019-02-01' AND o.Order_Date < '2019-03-01' ) > 0 AND
       SUM( o.Order_Date >= '2018-01-02' AND o.Order_Date < '2019-02-01' ) = 0 AND
       SUM( o.Order_Date < '2018-01-02' ) > 0 ;

这篇关于选择在特定时间段之前和之后存在的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！