Recently wrote SQL encountered a bit of a problem, how to query a continuous date data, wrote a very small data table, to illustrate my thinking. Hope to have an expert to guide the clearer ideas

Work: Started to encounter various database issues, the initial database, not very understanding, may be a lot of key database knowledge is not mastered

Now I'm going to express my problems in a simplified form. is to take data that appears on a continuous period of time.

Build a small table below to get data for the same kind of weather for three consecutive days

CREATE TABLE Weather (w_status VARCHAR (MAX), w_date DATETIME) INSERT into weather (w_status,w_date) VALUES (' Sunny ', ' 2017-3-18 Insert into weather (w_status,w_date) VALUES (' Clear ', ' 2017-3-19 ') insert into weather (w_status,w_date) VALUES (' Light rain ', ' 2017-3-20 ') insert into weather (w_status,w_date) VALUES (' drizzle ', ' 2017-3-21 ') insert into weather (w_status,w_date) VALUES ( ' Clear ', ' 2017-3-22 ') insert into weather (w_status,w_date) VALUES (' drizzle ', ' 2017-3-23 ') insert into weather (w_status,w_date) VALUES (' drizzle ', ' 2017-3-24 ') insert into weather (w_status,w_date) VALUES (' drizzle ', ' 2017-3-25 ') insert into weather (w_status,w _date) VALUES (' Yin ', ' 2017-3-26 ') INSERT into weather (w_status,w_date) values (' Yin ', ' 2017-3-27 ')

Because the data in this table is relatively small. So follow this small table to do the culling

DELETE from weather WHERE w_status in
(
SELECT A.w_status from weather as A GROUP by A.w_status have COUNT (*) <3
)

And then only the qinghe drizzle two states, respectively, to build a temporary table for storage, so that subsequent operations

CREATE TABLE  #TEMP1 (w_status varchar (max), w_date DATETIME) CREATE TABLE #TEMP2 (w_status varchar (max), w_date DATETIME) insert into #TEMP1 (W_status, w_date) SELECT * from weather as A WHERE a.w_status like '% clear% ' insert into #TEMP2 (w_s Tatus, W_date) SELECT * from weather as A WHERE a.w_status like '% drizzle% '

Then, the two temporary tables in different states are independently calculated for a continuous occurrence of data equal to three days, to see if there is such data, and to arrive at the date when the data appears.

CREATE TABLE #TEMP3 (w_date DATETIME) DECLARE @SD datetimedeclare @ED datetimeset @SD = (SELECT TOP 1 a.w_date from #TEMP1 as A ORDER by A.w_date ASC) SET @ED = (SELECT TOP 1 a.w_date from #TEMP1 as A order by A.w_date DESC) DECLARE @MARKDAY Datetimew Hile @SD <[email protected]beginif EXISTS (SELECT * from #TEMP1 as A WHERE a.w_date between @SD and DATEADD (day,2,@s D) GROUP by A.w_status have COUNT (*) =3) Beginset @[email protected]insert into #TEMP3 VALUES (@MARKDAY) SET @SD =date ADD (day,1, @SD) endelseset @SD =dateadd (day,1, @SD) end/******************************************* * Continuous weather conditions for different independent states /create TABLE #TEMP4 (w_date DATETIME) DECLARE @SD1 datetimedeclare @ED1  Datetimeset @SD1 = (select top 1 a.w_date from #TEMP2 as A ORDER by A.w_date ASC) SET @ED1 = (select top 1 a.w_date from #TEMP2 As A ORDER by A.w_date DESC) DECLARE @MARKDAY1 datetimewhile @SD1 <[email protected]beginif EXISTS (SELECT * from #T EMP2 as A WHERE a.w_date between @SD1 anD DATEADD (day,2, @SD1) GROUP by A.w_status have COUNT (*) =3) Beginset @[email protected]insert into #TEMP4 VALUES (@M ARKDAY1) SET @SD1 =dateadd (day,1, @SD1) endelseset @SD1 =dateadd (day,1, @SD1) endselect * from #TEMP3SELECT * from #TEMP4

So, got the light rain weather, appeared for three days in a row,

And the time for that state to appear is ' 2017-3-23 '

But personally think that I do this is the most stupid method, step-by-step to split the solution

The great God on the blog park,

The continuous date is asked. Or a certain regularity, such as the date of a continuous interval of two days. The same data state is present in this situation

How to use SQL to solve the problem correctly.

Recently wrote SQL encountered a bit of a problem, how to query a continuous date data, wrote a very small data table, to illustrate my thinking. Hope to have an expert to guide the clearer ideas