当前位置： 文江博客话题详情

MySQL 查询选择上周的数据？

发布于 2024-11-09 00:32:24 字数 312 浏览 1 评论 0原文

您好，我有一个带有日期字段和一些其他信息的表格。 我想选择过去一周的所有条目（一周从周日开始）。

表值：

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

我想选择上周的所有 id，预期输出为 5、6、8。 （id 2 不在上周，id 15 在本周。）

如何编写相同的 SQL 查询。

原文

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

How to write and SQL Query for the same.

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（23）

忆依然 2024-11-16 00:32:24
select id from tbname
where date between date_sub(now(),INTERVAL 1 WEEK) and now();

select id from tbname
where date between date_sub(now(),INTERVAL 1 WEEK) and now();
回复 原文
带上头具痛哭 2024-11-16 00:32:24
SELECT id FROM tbl
WHERE date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY

SELECT id FROM tbl
WHERE date >= curdate() - INTERVAL DAYOFWEEK(curdate())+6 DAY
AND date < curdate() - INTERVAL DAYOFWEEK(curdate())-1 DAY
回复 原文
沫雨熙 2024-11-16 00:32:24
SELECT id FROM table1
WHERE YEARWEEK(date) = YEARWEEK(NOW() - INTERVAL 1 WEEK)

我专门使用 YEARWEEK 函数返回到之前的整个日历周（而不是今天之前的 7 天）。 YEARWEEK 还允许使用第二个参数来设置一周的开始或确定如何处理一年中的第一周/最后一周。 YEARWEEK 允许您将回退/前进的周数保留在单个变量中，并且不会包含前/未来年份的相同周数，并且它比此处的大多数其他答案要短得多。

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.

回复 原文
二货你真萌 2024-11-16 00:32:24

简化形式：

上周数据：

SELECT id FROM tbl


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

2周前数据：

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

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:

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

回复 原文
天涯离梦残月幽梦 2024-11-16 00:32:24

如果您要检索过去 7 天内的记录，可以使用以下代码片段：

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

If you're looking to retrieve records within the last 7 days, you can use the snippet below:

SELECT date FROM table_name WHERE DATE(date) >= CURDATE() - INTERVAL 7 DAY;
回复 原文
月亮邮递员 2024-11-16 00:32:24

您可以在 php 中进行计算，然后将其添加到您的查询中：

$date = date('Y-m-d H:i:s',time()-(7*86400)); // 7 days ago

$sql = "SELECT * FROM table WHERE date <='$date' ";

现在这将给出一周前的日期

You can make your calculation in php and then add it to your query:

$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

回复 原文
无妨# 2024-11-16 00:32:24

也许最简单的方法是：

SELECT id
FROM table
WHERE date >= current_date - 7

8 天（即星期一 - 星期一）

Probably the most simple way would be:

SELECT id
FROM table
WHERE date >= current_date - 7

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

回复 原文
水波映月 2024-11-16 00:32:24

请大家...“上周”就像OP问的那样，我在哪里寻找（但没有找到令人满意的答案）是最后一周。

如果今天是星期二，则最后一周是星期一一周前到星期日一周前。

所以：

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

或者对于 ISO 周：

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

PLEASE people... 'Last week' like the OP asked and where I was looking for (but found none of answers satisfying) is THE LAST WEEK.

If today is Tuesday, then LAST WEEK is Monday A WEEK AGO to Sunday A WEEK AGO.

So:

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

Or for ISO weeks:

WHERE
    WEEK(yourdate, 3) = WEEK(NOW(), 3) - 1
回复 原文
谁的新欢旧爱 2024-11-16 00:32:24

这是在 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;

Here is a way to get last week, month, and year records in MySQL.

Last Week

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

Last Month

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

Last YEAR

SELECT UserName, InsertTime 
FROM tblaccounts
WHERE YEAR(InsertTime) = YEAR(NOW()) - 1;
回复 原文
后eg是否自 2024-11-16 00:32:24

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

然后，

select id
from table
where date >= "$sunday-date" + interval 7 DAY
  • 这可能也是在 MySQL 中获取相对于今天的星期日日期的一种方法；如果执行成本不是太高的话，这可以说是更清洁的解决方案

You'll need to calc which day relative to today is Sunday in your middleware (php, python, etc.)*

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
回复 原文
冬天的雪花 2024-11-16 00:32:24

它可以在一行中：

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

It can be in a single line:

SELECT * FROM table WHERE Date BETWEEN (NOW() - INTERVAL 7 DAY) AND NOW()
回复 原文
葵雨 2024-11-16 00:32:24

一种简单的方法可以是这个，这是我的代码中的一个真实示例，并且效果很好：

where("actions.created_at >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK)")

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)")
回复 原文
静待花开 2024-11-16 00:32:24

上面的查询将不起作用。
在 where 子句之后，如果我们不能对列值进行CAST，那么它将不起作用。您应该强制转换列值。

例如：

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

The above query will not work.
After the where clause, if we can not CAST the column value, then it will not work. You should cast the column value.

e.g.:

SELECT.....
WHERE CAST( yourDateColumn AS DATE ) > DATEADD( DAY, -7, CAST( GETDATE() AS DATE )
回复 原文
我最亲爱的 2024-11-16 00:32:24
SELECT id  FROM tb1
WHERE 
YEARWEEK (date) = YEARWEEK( current_date -interval 1 week ) 

SELECT id  FROM tb1
WHERE 
YEARWEEK (date) = YEARWEEK( current_date -interval 1 week )
回复 原文
囍孤女 2024-11-16 00:32:24

我也经常做一个快速的“上周”检查，以下内容对我来说效果很好，包括今天。

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-16 16:34:05.347 /* 上周一 */

@EndDate 2015-11-23 16:34:05.347 /* 本周一 */

如果我想要周日到周日，我会有以下内容。

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

@StartDate 2015-11-15 16:34:05.347 /* 上周日 */

@EndDate 2015-11-22 16:34:05.347 /* 上周日 */

I often do a quick "last week" check as well and the following tends to work well for me and includes today.

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

If you want this to NOT include today just subtract an extra day from the @EndDate. If I select these two variables today get

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

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

If I wanted Sunday to Sunday I would have the following.

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

@StartDate 2015-11-15 16:34:05.347 /* Previous Sunday */

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

回复 原文
掐死时间 2024-11-16 00:32:24
WHERE yourDateColumn > DATEADD(DAY, -7, GETDATE()) ;

WHERE yourDateColumn > DATEADD(DAY, -7, GETDATE()) ;
回复 原文
最冷一天 2024-11-16 00:32:24

你也可以用简单的方式使用它

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

You can also use it esay way

SELECT *
FROM   inventory
WHERE  YEARWEEK(`modify`, 1) = YEARWEEK(CURDATE(), 1)
回复 原文
缱倦旧时光 2024-11-16 00:32:24

我从周日开始的一周使用这个：

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

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
回复 原文
无所的.畏惧 2024-11-16 00:32:24

试试这个

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

-5 是从系统日期回溯 5 天，-12 是从系统日期回溯 12 天，对于此示例，星期三/或星期三到星期三无法回忆。

try this

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.

回复 原文
在梵高的星空下 2024-11-16 00:32:24

我的方式：

SELECT id
FROM tablename
WHERE date BETWEEN DATE_ADD(NOW(), INTERVAL -1 WEEK) AND NOW()

My way:

SELECT id
FROM tablename
WHERE date BETWEEN DATE_ADD(NOW(), INTERVAL -1 WEEK) AND NOW()
回复 原文
影子是时光的心 2024-11-16 00:32:24

试试这个：

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

Try this:

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
回复 原文
一百个冬季 2024-11-16 00:32:24

你可以试试这个。它对我有用：

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

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

You can try this one. it worked for me :

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

In the the above code createdtime is database field name, as individuals this name could vary.

回复 原文
淡墨 2024-11-16 00:32:24

如果您已经知道日期，那么您可以简单地使用 Between，如下所示：

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

If you already know the dates then you can simply use between, like this:

SELECT id    
FROM `Mytable`    
where MyDate BETWEEN "2011-05-15" AND "2011-05-21"
回复 原文
~没有更多了~

关于作者

只想待在家

暂无简介

0 文章
0 评论
22 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

linfzu01

文章 0 评论 0

§对你不离不弃

文章 0 评论 0

可遇━不可求

文章 0 评论 0

枕梦

文章 0 评论 0

qq_3LFa8Q

文章 0 评论 0

JP

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文