MySQL MySQL查询从上周选择数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6089960/
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 Query to select data from last week?
提问by coderex
Hi I have a table with a date field and some other information. I want to select all entries from the past week, (week start from Sunday).
嗨，我有一个带有日期字段和其他一些信息的表格。我想选择过去一周的所有条目（从星期日开始的一周）。
table values:
表值：
id date
2 2011-05-14 09:17:25
5 2011-05-16 09:17:25
6 2011-05-17 09:17:25
8 2011-05-20 09:17:25
15 2011-05-22 09:17:25
I want to select all ids from last week, expected output is 5, 6, 8. (id 2 not in last week, and id 15 is in current week.)
我想选择上周的所有 id，预期输出为 5、6、8。 （id 2 不在上周，id 15 在本周。）
How to write and SQL Query for the same.
怎么写和SQL Query一样。
回答by mr_eclair
select id from tbname
where date between date_sub(now(),INTERVAL 1 WEEK) and now();
回答by piotrm
SELECT id FROM tbl
WHERE date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
回答by timecrust
SELECT id FROM table1
WHERE YEARWEEK(date) = YEARWEEK(NOW() - INTERVAL 1 WEEK)
I use the YEARWEEK function specifically to go back to the prior whole calendar week (as opposed to 7 days before today). YEARWEEK also allows a second argument that will set the start of the week or determine how the first/last week of the year are handled. YEARWEEK lets you to keep the number of weeks to go back/forward in a single variable, and will not include the same week number from prior/future years, and it's far shorter than most of the other answers on here.
我专门使用 YEARWEEK 函数返回到之前的整个日历周（而不是今天之前的 7 天）。YEARWEEK 还允许设置一周的开始或确定如何处理一年的第一周/最后一周的第二个参数。YEARWEEK 允许您将要返回/向前的周数保留在单个变量中，并且不会包含前几年/未来几年的相同周数，并且它比此处的大多数其他答案要短得多。
回答by Anam
Simplified form:
简化形式：
Last week data:
上周数据：
SELECT id FROM tbl
WHERE
WEEK (date) = WEEK( current_date ) - 1 AND YEAR( date) = YEAR( current_date );
2 weeks ago data:
2周前数据：
SELECT id FROM tbl
WHERE
WEEK (date) = WEEK( current_date ) - 2 AND YEAR( date) = YEAR( current_date );
SQL Fiddle
SQL小提琴
回答by Ibu
You can make your calculation in php and then add it to your query:
您可以在 php 中进行计算，然后将其添加到您的查询中：
$date = date('Y-m-d H:i:s',time()-(7*86400)); // 7 days ago
$sql = "SELECT * FROM table WHERE date <='$date' ";
now this will give the date for a week ago
现在这将给出一周前的日期
回答by useless
Probably the most simple way would be:
可能最简单的方法是：
SELECT id
FROM table
WHERE date >= current_date - 7
For 8 days (i.e. Monday - Monday)
8天（即周一-周一）
回答by Abhinav bhardwaj
It can be in a single line:
它可以在一行中：
SELECT * FROM table WHERE Date BETWEEN (NOW() - INTERVAL 7 DAY) AND NOW()
回答by Javier Rodriguez Ortiz
A simple way can be this one, this is a real example from my code and works perfectly:
一种简单的方法可以是这种方法，这是我的代码中的一个真实示例，并且可以完美运行：
where("actions.created_at >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)")
回答by MS Berends
PLEASE people... 'Last week' like the OP asked and where I was looking for (but found none of answers satisfying) is THE LAST WEEK.
请人们......像OP所问的“上周”以及我正在寻找的地方（但没有找到令人满意的答案）是最后一周。
If today is Tuesday, then LAST WEEKis Monday A WEEK AGOto Sunday A WEEK AGO.
如果今天是星期二，那么LAST WEEK是从 Monday A WEEK A GO到 Sunday A WEEK A GO。
So:
所以：
WHERE
WEEK(yourdate) = WEEK(NOW()) - 1
Or for ISO weeks:
或 ISO 周：
WHERE
WEEK(yourdate, 3) = WEEK(NOW(), 3) - 1
回答by virtualeyes
You'll need to calc which day relative to today is Sunday in your middleware (php, python, etc.)*
您需要在中间件（php、python 等）中计算相对于今天的哪一天是星期天*
Then,
然后，
select id
from table
where date >= "$sunday-date" + interval 7 DAY
- may be a way to get sunday's date relative to today in MySQL as well; that would be arguably the cleaner solution if not too expensive to perform
- 也可能是在 MySQL 中获取相对于今天的星期日日期的一种方法；如果执行起来不太昂贵，那可以说是更清洁的解决方案
