SQL语句帮助 - 选择今天订购的客户 [英] SQL statement help -- Select customers who ordered today

问题描述

说我有一张表可以存储客户的订单ID。例如

|客户ID |订单ID |订单日期

如何获得今天订购的所有客户？

此外，OrderDate也将是DateTime。

像

  SELECT DISTINCT CustomerID 
 FROM TableName 
其中OrderDate>今天
  

但最后一部分是我无法想象的。

解决方案

只需要datetime的日期是相当普遍的 - 你应该能够使用Google的RDBMS的具体细节（因为你没有提到它）。重要的是通过转换今天的日期来查询 SARGable ¹ - 不是订单日期。

对于MSSQL，像

  SELECT DISTINCT CustomerID 
 FROM TableName 
  - 我假设你也想要午夜订单 - 所以使用> = 
其中OrderDate> = DATEADD（dd， 0，DATEDIFF（dd，0，GETDATE（）））
  

今天可以工作几天来自日期0（ DATEDIFF（dd，0，GETDATE（）））并将它们添加回到日期0（ DATEADD（dd，0，x ））。

¹ 如果您正在搜索任意日期，您仍然会转换这两个参数：

  SELECT DISTINCT CustomerID 
 FROM TableName 
其中
 OrderDate> = DATEADD（dd，0， DATEDIFF（dd，0，GETDATE（）））
  - 你*不要*第二天午夜，因为它会重复订单
 AND OrderDate< DATEADD（dd，0，DATEDIFF（dd，0，GETDATE（））+ 1）
  

Say I have a table which stores customers order IDs. Such as

| Customer ID | Order ID | Order Date

How can I get all customers who have ordered today?

Also OrderDate would be a DateTime.

Something like

SELECT DISTINCT CustomerID
FROM TableName
Where OrderDate > Today

But the last part is what I can't figure out.

解决方案

It's fairly common to only want a date out of a datetime - you should be able to Google for the specifics of your RDBMS (since you don't mention it). The important bit is to make your query SARGable by transforming today's date¹ - not the order date.

For MSSQL, something like

SELECT DISTINCT CustomerID
FROM TableName
--I assume you want midnight orders as well - so use >=
Where OrderDate >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

would work by taking number of days today is from Date 0 (DATEDIFF(dd, 0, GETDATE())) and adding them back to Date 0 (DATEADD(dd, 0, x)). That's T-SQL specific, though.

¹ If you were searching for an arbitrary date, you'd still transform both arguments:

SELECT DISTINCT CustomerID
FROM TableName
Where 
    OrderDate >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
    --You *do not* want midnight of the next day, as it would duplicate orders
    AND OrderDate < DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()) + 1)

这篇关于SQL语句帮助 - 选择今天订购的客户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！