（Oracle SQL）如何显示有关在两年内下订单的客户的信息？

I have a table named Orders where There are information about customers and their orders made and I want to retrieve information only customers made orders both 2009 as 2010. How Can I get it? I've code the following statement but I don't retrieve any information.

我有一个名为Orders的表格，其中有关于客户及其订单的信息，我想要检索仅2009年客户订购的信息。我怎样才能得到它？我编写了以下语句，但我没有检索任何信息。

SELECT   customer_id
FROM     cs_ordenes
WHERE    order_date BETWEEN '01/01/2009' AND '31/12/2009'
AND      order_date BETWEEN '01/01/2010' AND '31/12/2010'
ORDER BY customer_id DESC;

4 个解决方案

#1

You can use aggregation to check if there is at least one order in each year.

您可以使用聚合来检查每年是否至少有一个订单。

select customer_id
from cs_ordenes
where order_date >= date '2009-01-01'
    and order_date < date '2011-01-01'
group by customer_id
having count(distinct to_char(order_date, 'YYYY')) = 2

count(distinct to_char(order_date, 'YYYY')) finds number distinct year from order date for each customer. In our case and since we have already filter the data for date range of two year using the WHERE clause, if the count is 2, then it means there was at least one order in each year.

count（distinct to_char（order_date，'YYYY'））从每个客户的订单日期中查找不同年份的数字。在我们的情况下，由于我们已经使用WHERE子句过滤了两年的日期范围的数据，如果计数为2，则表示每年至少有一个订单。

#2

The same order_date can be both in 2009 and 2010, so no rows are returned. One approach could be to count the distinct number of years each customer's orders have:

相同的order_date可以在2009年和2010年，因此不返回任何行。一种方法可能是计算每个客户订单的不同年数：

SELECT   customer_id
FROM     cs_ordenes
WHERE    EXTRACT(YEAR FROM order_date) IN (2009, 2010)
GROUP BY customer_id
HAVING   COUNT(DISTINCT(EXTRACT(YEAR FROM order_date)) = 2
ORDER BY customer_id DESC;

#3

" Select customer_id from orders Where order_ date between To_date('01-01-2009','dd-mm-yyyy) and to_date('01-01-2010','dd-mm-yyyy')

“从订单中选择customer_id，其中order_ date介于To_date（'01 -01-2009'，'dd-mm-yyyy）和to_date（'01 -01-2010'，'dd-mm-yyyy'）之间

#4

How about simplifying the WHERE clause?

如何简化WHERE子句？

SELECT customer_id
FROM cs_ordenes
WHERE order_date >= DATE '2009-01-01' AND
      order_date < DATE '2011-01-01'
ORDER BY customer_id DESC;

Your original logic would work if you changed the AND to OR between the two statements. It is impossible for a single date to be in 2009 and 2011, so your query returns no rows. The above logic is simpler, though.

如果您将两个语句之间的AND更改为OR，则原始逻辑将起作用。单个日期不可能在2009年和2011年，因此您的查询不返回任何行。但是，上述逻辑更简单。

Also, BETWEEN is dangerous to use with dates, simply because sometimes they have time components. That is why I prefer the simple inequalities.

此外，BETWEEN与日期一起使用很危险，仅仅因为它们有时间成分。这就是为什么我更喜欢简单的不等式。

#1