​

preface

Today we encountered a very bad problem again, because the start date of foreign friends is Sunday, different from ours, our start date of every week is Monday, so I need to use mysql to collect the data of a week, for us, the query records include: The record of Last Sunday + the data of this week to Saturday, this does not meet our requirements, the data of Last Sunday is also included.

So is also to find a long time of information, finally solved

I. Problem discovery

By default, the test I originally wrote to query data from the previous week looks like this:

Select a.sushenum,cast(a.dfdata as DECIMAL (10,3)) dfdatanumber, a.dfdata, Cast (a. d. fmoney as a DECIMAL (10, 3)) dfmoneynumber, a. d. fmoney, DATE_FORMAT (a. RRQ, '% % Y - m - H: % d % % I: % S') LRRQ the from Tablename_test A where YEARWEEK(date_format(a.rrq,'%Y-%m-%d')) = YEARWEEK(now()) and a.sushenum = '1309 A.sushenum,cast(a.fdata as DECIMAL (10,3)) dfdatanumber, a.fdata, Cast (a. d. fmoney as a DECIMAL (10, 3)) dfmoneynumber, a. d. fmoney, DATE_FORMAT (a. RRQ, '% % Y - m - H: % d % % I: % S') LRRQ the from tablename_test A where YEARWEEK(date_format(A.lrrq,'%Y-%m-%d')) = YEARWEEK(now())-1 and A.sushenum = '1309'Copy the code

By the way, query the difference between this week and last week, you can compare the above two SQL statements, the difference is

This week is YEARWEEK (now ()) – 0

Last week was YEARWEEK (now () – 1

The week before is YEARWEEK(now())-2, and so on.

The queried record is

​

And you can obviously see that the records for December 2, 2018 are also coming up, and December 2 was a Sunday. And just to make it more intuitive, let me cut out the months of December

​

So the records that are queried in this way are problematic for us. I’m going to show you the solution.

Second, problem solving

Can know, mysql query, this week is used last week YEARWEEK () the function, specific tutorial can see link: www.runoob.com/mysql/mysql…

​

If not, the default is Sunday. For our system, if Monday is the start date of the week, let’s set mode to 1.

Revised:

Select a.sushenum,cast(a.dfdata as DECIMAL (10,3)) dfdatanumber, a.dfdata, Cast (a. d. fmoney as a DECIMAL (10, 3)) dfmoneynumber, a. d. fmoney, DATE_FORMAT (a. RRQ, '% % Y - m - H: % d % % I: % S') LRRQ the from Tablename_test A where YEARWEEK(date_format(a.rrq,'%Y-%m-%d'),1) = YEARWEEK(now(),1) and a.sushenum = '1309' Select a.sushenum,cast(a.dfdata as DECIMAL (10,3)) dfdatanumber, a.dfdata, Cast (a. d. fmoney as a DECIMAL (10, 3)) dfmoneynumber, a. d. fmoney, DATE_FORMAT (a. RRQ, '% % Y - m - H: % d % % I: % S') LRRQ the from tablename_test A where YEARWEEK(date_format(A.lrrq,'%Y-%m-%d'),1) = YEARWEEK(now(),1)-1 and A.sushenum = '1309'Copy the code

After the modification, the following records are displayed:

​

You can compare the above picture of the query record, you can see that the record of December 2 is gone, but the record of December 3 is gone, so it is solved.

Third, summary

So, when you use SQL functions, make sure to look at the API of this function, so that you can use this function more smoothly than others.

So here is a question, oracle how to query this week, last week records?

Reference article:

Blog.csdn.net/qq_21995733…

www.cnblogs.com/xgwtzg/p/61…

Thanks for the original author’s sharing, so that technical people can solve the problem faster

It encountered problems on December 31, 2018

Today, when I tested the program by myself, I found that the data could not be queried, so I hurried to investigate. I found a big problem.

Mysql: Yearweek () : yearweek() : yearweek() : yearweek()

I also talked about the use of the Yearweek () function.

Yearweek (Date, mode) returns the year and week(0-53), where 0 represents day of the week and 1 represents Monday

​​

As you can see, on December 31, it was 201901 (the first week of 2019).

The reason is that it’s New Year’s Eve this week, so it shows the first week of 2019.

​

But when I looked up last week, I used YEARWEEK(now())-1

​

You can see that it changed to 201900, but I want to query the data of last week, which is December 24 to 30,

​

The value returned by YearWeek () is 201851, so it is in use

Select a.sushenum,cast(a.dfdata as DECIMAL (10,3)) dfdatanumber, a.dfdata, Cast (a. d. fmoney as a DECIMAL (10, 3)) dfmoneynumber, a. d. fmoney, DATE_FORMAT (a. RRQ, '% % Y - m - H: % d % % I: % S') LRRQ the from tablename_test A where YEARWEEK(date_format(A.lrrq,'%Y-%m-%d'),1) = YEARWEEK(now(),1)-1 and A.sushenum = '1309Copy the code

YEARWEEK(date_format(a.lrq,’%Y-%m-%d’),1) =YEARWEEK(now(),1)-1

That’s a big problem, and that’s why last week’s data showed no.

So I have not thought of a solution for the moment, the follow-up continue to study, first put the problem here, which god solved or have solutions or methods, you can leave a message or link to the article at the bottom. I will add to this article when I think of a solution.

​