php mysql 选择日期时间字段小于指定值的所有记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11605166/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql select all records where a datetime field is less than a specified value
提问by Greg
So I am trying to run a query that will select, in this case, clients that haven't had an appointment is X amount of time (2 weeks for instance). It boils down to "show me a list of clients that haven't had an appointment in 2 weeks". I'm attempting to solve this in php by doing something like:
所以我试图运行一个查询，在这种情况下，没有预约的客户是 X 时间（例如 2 周）。归结为“向我展示 2 周内未预约的客户列表”。我正在尝试通过执行以下操作在 php 中解决此问题：
$date = new DateTime;
$ago = new DateInterval('P2W');
$target = $date->sub($ago);
//query to select clients that aren't scheduled after the $target date
$clients = ...;
Two tables involved, appt_tbl, and clients_tbl. appt_tbl stores a client_id for each appointment record.
涉及两个表，appt_tbl和clients_tbl。appt_tbl 为每个约会记录存储一个 client_id。
So essentially what I need is to select the "max" appointment for each client, and if it's < my $target date, include them in the query results. I've tried various flavors of queries, queries with sub queries, but I'm stumbling on getting this query right.
所以基本上我需要的是为每个客户选择“最大”约会，如果它是 < 我的 $target 日期，请将它们包含在查询结果中。我已经尝试了各种风格的查询、带有子查询的查询，但是我在正确获取这个查询方面遇到了困难。
My current attempt looks something like:
我目前的尝试看起来像：
SELECT *
FROM clients_tbl
INNER JOIN
(
SELECT client_id
FROM appt_tbl
WHERE MAX(appt_date_time) < '2012-07-22'
GROUP BY client_id
) appts ON appts.client_id = clients_tbl.client_id;
This should also include clients that have never been scheduled (IE won't appear in the appt_tbl), but not clients who have an appointment booked in the next two weeks.
这还应包括从未安排过的客户（IE 不会出现在 appt_tbl 中），但不包括在接下来的两周内已预约的客户。
回答by Zane Bien
SELECT a.*
FROM clients_tbl a
LEFT JOIN appt_tbl b ON
a.client_id = b.client_id AND
b.appt_date_time >= CURDATE() - INTERVAL 2 WEEK
WHERE b.client_id IS NULL
What this query does first (before the WHEREfiltering) is select all clients whether or not they have a scheduled appointment greater than two weeks ago.
此查询首先（在WHERE过滤之前）做的是选择所有客户，无论他们是否有超过两周前的预定约会。
If the client does not have an appointment greater than two weeks ago, the values in the joined table will be NULL. We wantall rows where the join conditions did not satisfy (i.e. values in joined table are null), which is done with WHERE b.client_id IS NULL.
如果客户在两周前没有约会，则联接表中的值将为NULL。我们想要连接条件不满足的所有行（即连接表中的值为空），这是用WHERE b.client_id IS NULL.
This also includes into the result-set clients who do not have any corresponding appointments at all.
这也包括在结果集中根本没有任何相应约会的客户。
Clients who have appointments in the future are excluded.
不包括将来有约会的客户。
There is also no need to construct a datetime string in PHP. You can simply do it straight in the query (although you may pass in the number of weeks ago as a parameter).
也不需要在 PHP 中构造日期时间字符串。您可以直接在查询中执行此操作（尽管您可以将周数作为参数传入）。
回答by Vivek
This could be simple enough i think.
我认为这可能很简单。
select users from time_sheet where created_date >= date('2013-12-01');
In your case you got to do like this. Instead of this
在你的情况下，你必须这样做。而不是这个
WHERE MAX(appt_date_time) < '2012-07-22'
do this
做这个
WHERE MAX(appt_date_time) < date('2012-07-22')
That acually makes the date comparision, earlier one was date with string comparision.
这实际上是进行日期比较，较早的一个是日期与字符串比较。
Cheers!!
干杯！！
