从上周选择数据的 MySQL 查询？

您需要在中间件(php、 python 等) * 中计算相对于今天的哪一天是星期天

然后,

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

您可以使用 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

SELECT id FROM tbl
WHERE date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY

如果你已经知道日期，那么你可以简单地使用，像这样:

SELECT id
FROM `Mytable`
where MyDate BETWEEN "2011-05-15" AND "2011-05-21"

select id from tbname
where date between date_sub(now(),INTERVAL 1 WEEK) and now();

简体表格:

Last week data:

SELECT id FROM tbl




WHERE
WEEK (date) = WEEK( current_date ) - 1 AND YEAR( date) = YEAR( current_date );

两周前的数据:

SELECT id FROM tbl




WHERE
WEEK (date) = WEEK( current_date ) - 2 AND YEAR( date) = YEAR( current_date );

SQL Fiddle

http://sqlfiddle.com/#!8/6fa6e/2

SELECT id  FROM tb1
WHERE
YEARWEEK (date) = YEARWEEK( current_date -interval 1 week )

我经常做一个快速的“上周”检查，以下往往对我很有用，包括今天。

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME


SET @StartDate = Getdate() - 7 /* Seven Days Earlier */
SET @EndDate = Getdate() /* Now */


SELECT id
FROM   mytable
WHERE  date BETWEEN @StartDate AND @Enddate

如果您希望这个不包括今天，只需从@EndDate 中减去额外的一天。如果我今天选择这两个变量

@ StartDate 2015-11-1616:34:05.347/* Last Monday */

@ EndDate 2015-11-2316:34:05.347/* This Monday */

如果我想要星期天到星期天，我会有以下。

SET @StartDate = Getdate() - 8 /* Eight Days Earlier */
SET @EndDate = Getdate() - 1  /* Yesterday */

@ StartDate 2015-11-1516:34:05.347/

@ EndDate 2015-11-2216:34:05.347/* Last Sunday */

试试这个:

Declare @Daytype varchar(15),
@StartDate datetime,
@EndDate datetime
set @Daytype = datename(dw, getdate())


if @Daytype= 'Monday'
begin
set @StartDate = getdate()-7
set @EndDate = getdate()-1


end




else if @Daytype = 'Tuesday'


begin
set @StartDate = getdate()-8
set @EndDate = getdate()-2


end
Else if @Daytype = 'Wednesday'
begin
set @StartDate = getdate()-9
set @EndDate = getdate()-3
end
Else if @Daytype = 'Thursday'
begin
set @StartDate = getdate()-10
set @EndDate = getdate()-4
end


Else if @Daytype = 'Friday'


begin
set @StartDate = getdate()-11
set @EndDate = getdate()-5


end


Else if @Daytype = 'Saturday'


begin
set @StartDate = getdate()-12
set @EndDate = getdate()-6


end


Else if @Daytype = 'Sunday'


begin
set @StartDate = getdate()-13
set @EndDate = getdate()-7


end


select @StartDate,@EndDate

也许最简单的方法是:

SELECT id
FROM table
WHERE date >= current_date - 7

For 8 days (i.e. Monday - Monday)

SELECT id FROM table1
WHERE YEARWEEK(date) = YEARWEEK(NOW() - INTERVAL 1 WEEK)

我使用 YARWEEK 函数专门回到上一个日历周(而不是今天之前的7天)。YARWEEK 还允许第二个参数来设置一周的开始或决定如何处理一年中的第一周/最后一周。YARWEEK 允许你在一个变量中保留回溯/向前的周数，并且不包括之前/未来年份的相同周数，而且它比这里的大多数其他答案都要短得多。

它可以排成一行:

SELECT * FROM table WHERE Date BETWEEN (NOW() - INTERVAL 7 DAY) AND NOW()

WHERE yourDateColumn > DATEADD(DAY, -7, GETDATE()) ;

上述查询将不起作用。 在 where子句之后，如果我们不能 CAST列值，那么它将不工作。应该使用 cast列值。

例如:

SELECT.....
WHERE CAST( yourDateColumn AS DATE ) > DATEADD( DAY, -7, CAST( GETDATE() AS DATE )

You can also use it esay way

SELECT *
FROM   inventory
WHERE  YEARWEEK(`modify`, 1) = YEARWEEK(CURDATE(), 1)

i Use this for the week start from SUNDAY:

SELECT id FROM tbl
WHERE
date >= curdate() - INTERVAL DAYOFWEEK(curdate())+5 DAY
AND date < curdate() - INTERVAL DAYOFWEEK(curdate())-2 DAY

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)")

请人们... “上周”，像 OP 问，我在哪里寻找(但没有找到令人满意的答案)是最后一周。

如果今天是星期二，那么 LAST WEEK是星期一 一周前到星期日 一周前。

所以:

WHERE
WEEK(yourdate) = WEEK(NOW()) - 1

或 ISO 周:

WHERE
WEEK(yourdate, 3) = WEEK(NOW(), 3) - 1

比如上个月，去年，最后15天，最后3个月

SELECT name, created_at
FROM employees
WHERE
YEARWEEK(`created_at`, 1) = YEARWEEK( CURDATE() - INTERVAL 1 WEEK, 1)

下面是一种在 MySQL 中获取上周、月份和年份记录的方法。

上周

SELECT UserName, InsertTime
FROM tblaccounts
WHERE WEEK(InsertTime) = WEEK(NOW()) - 1
AND MONTH(InsertTime) = MONTH(NOW())
AND YEAR(InsertTime) = YEAR(NOW())

上个月

SELECT UserName, InsertTime
FROM tblaccounts
WHERE MONTH(InsertTime) = MONTH(NOW()) - 1
AND YEAR(InsertTime) = YEAR(NOW())

去年

SELECT UserName, InsertTime
FROM tblaccounts
WHERE YEAR(InsertTime) = YEAR(NOW()) - 1;

如果你想检索过去7天内的记录，你可以使用下面的代码片段:

SELECT date FROM table_name WHERE DATE(date) >= CURDATE() - INTERVAL 7 DAY;

你可以试试这个，对我很有效:

where date(createdtime) <= date(curdate())-7

在上面的代码 createdtime是数据库字段名，作为个体，这个名称可能会有所不同。

试试这个

WHERE trunc(DATE) <= (trunc (sysdate) -5)  AND trunc(DATE) >= (trunc (sysdate) -12)

-5 is 5 days back from system date ,, -12 is 12 days back from system date for this example wednesday / or sednesday to wednesday cant recall.