MySQL MySQL选择过去7天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24272335/
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 last 7 days
提问by karadayi
I read some Posts here and seems like nothing special but I can not still select the entries of the last days.
我在这里阅读了一些帖子，似乎没什么特别的，但我仍然无法选择最后几天的条目。
SELECT
p1.kArtikel,
p1.cName,
p1.cKurzBeschreibung,
p1.dLetzteAktualisierung,
p1.dErstellt,
p1.cSeo,
p2.kartikelpict,
p2.nNr,
p2.cPfad
FROM
tartikel AS p1 WHERE DATE(dErstellt) > (NOW() - INTERVAL 7 DAY)
INNER JOIN
tartikelpict AS p2
ON (p1.kArtikel = p2.kArtikel) WHERE (p2.nNr = 1)
ORDER BY
p1.kArtikel DESC
LIMIT
100;', $connection);
If I add the between today and last 7 days my Code will not output anything.
如果我在今天和过去 7 天之间添加，我的代码将不会输出任何内容。
回答by spencer7593
The WHEREclause is misplaced, it has to follow the table references and JOIN operations.
该WHERE子句放错了位置，它必须遵循表引用和 JOIN 操作。
Something like this:
像这样的东西：
FROM tartikel p1
JOIN tartikelpict p2
ON p1.kArtikel = p2.kArtikel
AND p2.nNr = 1
WHERE p1.dErstellt >= DATE(NOW()) - INTERVAL 7 DAY
ORDER BY p1.kArtikel DESC
EDIT(three plus years later)
编辑（三年多后）
The above essentially answers the question "I tried to add a WHERE clause to my query and now the query is returning an error, how do I fix it?"
上面基本上回答了“我尝试向查询添加 WHERE 子句，现在查询返回错误，我该如何解决？”的问题。
As to a question about writing a condition that checks a date range of "last 7 days"...
关于编写检查“过去 7 天”日期范围的条件的问题......
That really depends on interpreting the specification, what the datatype of the column in the table is (DATE or DATETIME) and what data is available... what should be returned.
这实际上取决于对规范的解释，表中列的数据类型是什么（DATE 或 DATETIME）以及哪些数据可用......应该返回什么。
To summarize: the general approach is to identify a "start" for the date/datetime range, and "end" of that range, and reference those in a query. Let's consider something easier... all rows for "yesterday".
总结一下：一般方法是确定日期/日期时间范围的“开始”和该范围的“结束”，并在查询中引用这些。让我们考虑更简单的事情……“昨天”的所有行。
If our column is DATE type. Before we incorporate an expression into a query, we can test it in a simple SELECT
如果我们的列是 DATE 类型。在我们将表达式合并到查询中之前，我们可以在一个简单的 SELECT 中测试它
SELECT DATE(NOW()) + INTERVAL -1 DAY
and verify the result returned is what we expect. Then we can use that same expression in a WHERE clause, comparing it to a DATE column like this:
并验证返回的结果是我们所期望的。然后我们可以在 WHERE 子句中使用相同的表达式，将其与 DATE 列进行比较，如下所示：
WHERE datecol = DATE(NOW()) + INTERVAL -1 DAY
For a DATETIME or TIMESTAMP column, we can use >=and <inequality comparisons to specify a range
对于DATETIME或TIMESTAMP列，我们可以使用>=和<不平等的比较来指定一个范围
WHERE datetimecol >= DATE(NOW()) + INTERVAL -1 DAY
AND datetimecol < DATE(NOW()) + INTERVAL 0 DAY
For "last 7 days" we need to know if that mean from this point right now, back 7 days ... e.g. the last 7*24 hours , including the time component in the comparison, ...
对于“过去 7 天”，我们需要知道这是否意味着从现在开始，回到 7 天……例如过去 7*24 小时，包括比较中的时间部分，……
WHERE datetimecol >= NOW() + INTERVAL -7 DAY
AND datetimecol < NOW() + INTERVAL 0 DAY
the last seven complete days, not including today
最近七个完整的天，不包括今天
WHERE datetimecol >= DATE(NOW()) + INTERVAL -7 DAY
AND datetimecol < DATE(NOW()) + INTERVAL 0 DAY
or past six complete days plus so far today ...
或过去六天加上今天到目前为止......
WHERE datetimecol >= DATE(NOW()) + INTERVAL -6 DAY
AND datetimecol < NOW() + INTERVAL 0 DAY
I recommend testing the expressions on the right side in a SELECT statement, we can use a user-defined variable in place of NOW() for testing, not being tied to what NOW() returns so we can test borders, across week/month/year boundaries, and so on.
我建议在 SELECT 语句中测试右侧的表达式，我们可以使用用户定义的变量代替 NOW() 进行测试，而不是与 NOW() 返回的内容相关联，因此我们可以跨周/月测试边界/年界限，等等。
SET @clock = '2017-11-17 11:47:47' ;
SELECT DATE(@clock)
, DATE(@clock) + INTERVAL -7 DAY
, @clock + INTERVAL -6 DAY
Once we have expressions that return values that work for "start" and "end" for our particular use case, what we mean by "last 7 days", we can use those expressions in range comparisons in the WHERE clause.
一旦我们的表达式返回适用于我们特定用例的“开始”和“结束”的值，即“过去 7 天”的意思，我们就可以在 WHERE 子句的范围比较中使用这些表达式。
(Some developers prefer to use the DATE_ADDand DATE_SUBfunctions in place of the + INTERVAL val DAY/HOUR/MINUTE/MONTH/YEARsyntax.
（一些开发人员更喜欢使用DATE_ADD和DATE_SUB函数来代替+ INTERVAL val DAY/HOUR/MINUTE/MONTH/YEAR语法。
And MySQL provides some convenient functions for working with DATE, DATETIME and TIMESTAMP datatypes... DATE, LAST_DAY,
MySQL 提供了一些方便的函数来处理 DATE、DATETIME 和 TIMESTAMP 数据类型... DATE、LAST_DAY、
Some developers prefer to calculate the start and end in other code, and supply string literals in the SQL query, such that the query submitted to the database is
一些开发人员更喜欢在其他代码中计算开始和结束，并在 SQL 查询中提供字符串字面量，这样提交到数据库的查询是
WHERE datetimecol >= '2017-11-10 00:00'
AND datetimecol < '2017-11-17 00:00'
And that approach works too. (My preference would be to explicitly cast those string literals into DATETIME, either with CAST, CONVERT or just the + INTERVAL trick...
这种方法也有效。（我的偏好是将这些字符串文字显式转换为 DATETIME，使用 CAST、CONVERT 或仅使用 + INTERVAL 技巧......
WHERE datetimecol >= '2017-11-10 00:00' + INTERVAL 0 SECOND
AND datetimecol < '2017-11-17 00:00' + INTERVAL 0 SECOND
The above all assumes we are storing "dates" in appropriate DATE, DATETIME and/or TIMESTAMP datatypes, and not storing them as strings in variety of formats e.g. 'dd/mm/yyyy', m/d/yyyy, julian dates, or in sporadically non-canonical formats, or as a number of seconds since the beginning of the epoch, this answer would need to be much longer.
以上都假设我们将“日期”存储在适当的 DATE、DATETIME 和/或 TIMESTAMP 数据类型中，而不是将它们存储为各种格式的字符串，例如'dd/mm/yyyy', m/d/yyyy, julian 日期，或零星的非规范格式，或许多自纪元开始以来的几秒钟，这个答案需要更长的时间。
回答by fthiella
Since you are using an INNER JOIN you can just put the conditions in the WHERE clause, like this:
由于您使用的是 INNER JOIN，您可以将条件放在 WHERE 子句中，如下所示：
SELECT
p1.kArtikel,
p1.cName,
p1.cKurzBeschreibung,
p1.dLetzteAktualisierung,
p1.dErstellt,
p1.cSeo,
p2.kartikelpict,
p2.nNr,
p2.cPfad
FROM
tartikel AS p1 INNER JOIN tartikelpict AS p2
ON p1.kArtikel = p2.kArtikel
WHERE
DATE(dErstellt) > (NOW() - INTERVAL 7 DAY)
AND p2.nNr = 1
ORDER BY
p1.kArtikel DESC
LIMIT
100;
