MySQL 查询以获取上个月的所有行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2090221/
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
Query to get all rows from previous month
提问by lewisqic
I need to select all rows in my database that were created last month.
我需要选择上个月创建的数据库中的所有行。
For example, if the current month is January, then I want to return all rows that were created in December, if the month is February, then I want to return all rows that were created in January. I have a date_createdcolumn in my database that lists the date created in this format: 2007-06-05 14:50:17.
例如，如果当前月份是一月，那么我想返回在十二月创建的所有行，如果月份是二月，那么我想返回一月创建的所有行。date_created我的数据库中有一个列，列出了以这种格式创建的日期：2007-06-05 14:50:17.
回答by hobodave
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
回答by martin clayton
Here's another alternative. Assuming you have an indexed DATEor DATETIMEtype field, this should use the index as the formatted dates will be type converted before the index is used. You should then see a rangequery rather than an indexquery when viewed with EXPLAIN.
这是另一种选择。假设您有一个索引DATE或DATETIME类型字段，这应该使用索引，因为格式化日期将在使用索引之前进行类型转换。然后，当使用EXPLAIN查看时，您应该看到一个range查询而不是一个index查询。
SELECT
*
FROM
table
WHERE
date_created >= DATE_FORMAT( CURRENT_DATE - INTERVAL 1 MONTH, '%Y/%m/01' )
AND
date_created < DATE_FORMAT( CURRENT_DATE, '%Y/%m/01' )
回答by ekerner
If there are no future dates ...
如果没有未来的日期......
SELECT *
FROM table_name
WHERE date_created > (NOW() - INTERVAL 1 MONTH);
Tested.
测试。
回答by SMTF
Alternatively to hobodave's answer
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
You could achieve the same with EXTRACT, using YEAR_MONTH as unit, thus you wouldn't need the AND, like so:
您可以使用 EXTRACT 实现相同的效果，使用 YEAR_MONTH 作为单位，因此您不需要 AND，如下所示：
SELECT * FROM table
WHERE EXTRACT(YEAR_MONTH FROM date_created) = EXTRACT(YEAR_MONTH FROM CURDATE() - INTERVAL
1 MONTH)
回答by Gregg
SELECT *
FROM yourtable
where DATE_FORMAT(date_created, '%Y-%m') = date_format(DATE_SUB(curdate(), INTERVAL 1 month),'%Y-%m')
This should return all the records from the previous calendar month, as opposed to the records for the last 30 or 31 days.
这应该返回上一个日历月的所有记录，而不是过去 30 或 31 天的记录。
回答by Ghazanfar Mir
Even though the answer for this question has been selected already, however, I believe the simplest query will be
尽管已经选择了这个问题的答案，但是，我相信最简单的查询将是
SELECT *
FROM table
WHERE
date_created BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE();
回答by saqib jahangir Pakistan
Here is the query to get the records of the last month:
这是获取上个月记录的查询：
SELECT *
FROM `tablename`
WHERE `datefiled`
BETWEEN DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH )
AND
LAST_DAY( DATE_SUB( DATE( NOW( ) ) , INTERVAL 1
MONTH ) )
Regards - saqib
问候 - 萨基布
回答by Giles
WHERE created_date >= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH)), INTERVAL 1 DAY)
AND created_date <= DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH)), INTERVAL 0 DAY)
This worked for me (Selects all records created from last month, regardless of the day you run the query this month)
这对我有用（选择上个月创建的所有记录，无论您本月运行查询的日期如何）
回答by ggiroux
select fields FROM table
WHERE date_created LIKE concat(LEFT(DATE_SUB(NOW(), interval 1 month),7),'%');
select fields FROM table
WHERE date_created LIKE concat(LEFT(DATE_SUB(NOW(), interval 1 month),7),'%');
this one will be able to take advantage of an index if your date_created is indexed, because it doesn't apply any transformation function to the field value.
如果您的 date_created 被索引，这个将能够利用索引，因为它不会对字段值应用任何转换函数。
回答by sid busa
SELECT * FROM table
WHERE YEAR(date_created) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
AND MONTH(date_created) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
