What are the problems that have occurred continuously for N days in SQL?

This article mainly introduces "what are the continuous N-day problems in SQL". In daily operation, I believe that many people have doubts about the problems in SQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the questions of "what are the problems that appear in continuous N days in SQL?" Next, please follow the editor to study!

There is a gymnasium where daily traffic information is recorded in these three columns of information: serial number (id), date (date), and crowd flow (people). Please write a query statement to find out the peak period, which is required for three consecutive days or more, and the daily flow of people is not less than 100. For example, table stadium:

For the example data above, the output is:

The problem and the desired results are already known, how to solve it? Here are two ways to solve the problem

Test environment

SQL Server 2017

Idea 1: find the date difference

By solving the date difference before and after a date is 1 and-1 respectively, we can know that the three records before and after this date are continuous. The popular point is that today minus the difference of the previous day is 1, and today minus tomorrow is-1, then the date of yesterday, today and tomorrow is three consecutive days in a row. The specific solution is as follows:

SELECT DISTINCT d.* FROM stadium djimg a WHERE ABS (DATEDIFF (DAY,d.date,a.date)) 99 AND b.people > 99 AND c.people > 99)

(hint: you can swipe the code left and right)

Idea 2: permutation and combination

According to the continuity of id, there are three kinds of situations: the combination judgment is divided into three tables, S1, Percience, S2, and S3.

(1) s. 1. 1. 1. 2. 1. 2. 3. 1. 1, which is equivalent to the sequence of S3, S2, S1.

(2) s. 2. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 1. 2, which is equivalent to the sequence of S3, S1, S2.

(3) s3.idmurs2.idcolors 1, which is equivalent to the sequence of S1 / s2 / S3.

The specific solution is as follows:

Select DISTINCT s1.* from stadium S1, stadium S2, stadium S3 where s1.people > = 100and s2.people > = 100and s3.people > = 100and ((s1.id-s2.id = 1 and s2.id-s3.id = 1) or (s2.id-s1.id = 1 and s1.id-s3.id = 1) or (s3.id-s2.id = 1 and s2.id-s1.id = 1) order by s1.id

However, this method is limited to the suitable scene, if more than 3 days, the continuous arrangement and combination will increase, the effect is not very good.

At this point, the study on "what are the problems in SQL for consecutive N days" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!