Question

Given a table with the following two columns 1) Date, DATE 2) Weather, INTEGER (0=ClearDay, 1=Rain, 2=Snow)

我像一个“选举”一样,在雨季之后的——+(N,M)天之内,所有回返日期都好。 I.e.,如果(+)N,M =,2 并且今天降水(Wed)的话,我今天的希望如果在从Tue到Fri的任何一天都知道的话,就回去了,假设每天只有一种天气价值。

Example 1:
1/1/2011, 0
1/1/2011, 0
1/2/2011, 2
1/3/2011, 1
1/4/2011, 1
1/5/2011, 0
1/6/2011, 0

With (-+)N,M=2,1 - Return 1/3, 1/4

Example 2:
1/1/2011, 0
1/2/2011, 2
1/3/2011, 1
1/4/2011, 1
1/5/2011, 1
1/6/2011, 0
1/7/2011, 2

With (-+)N,M=1,2, Return 1/3, 1/5

Answer 1

你们应该这样做。替换@N,改为编号前几天和@ M,在检查后天数。请注意,我假定您的表格名称为t。

SELECT DISTINCT rain.date
FROM t AS rain
JOIN t AS snow ON snow.date between rain.date - @N AND rain.date + @M 
    AND snow.weather = 2
WHERE rain.weather = 1

I m having trouble deciding what the correct way is to compare the dates in sqlite. I ve tried looking it up but I m getting conflicting advice. This sqlite doc page made it sound like julianday was the best way to compare just the DAY part of a DATE. If the above query doesn t work for you, try this version instead which uses julianday to get just the DAY component of the date.

SELECT DISTINCT rain.date
FROM t AS rain
JOIN t AS snow ON julianday(snow.date) between julianday(rain.date) - @N AND julianday(rain.date) + @M 
    AND snow.weather = 2
WHERE rain.weather = 1

Answer 2

你们可以通过做非等工作来做到这一点。 (在Oracle案中,我确实需要 t击。)

CREATE TABLE temp (
weather_date DATE,
weather NUMBER)
;

INSERT ALL
INTO temp (weather_date, weather) VALUES ( 1-jan-2011 , 0)
INTO temp (weather_date, weather) VALUES ( 2-jan-2011 , 2)
INTO temp (weather_date, weather) VALUES ( 3-jan-2011 , 1)
INTO temp (weather_date, weather) VALUES ( 4-jan-2011 , 1)
INTO temp (weather_date, weather) VALUES ( 5-jan-2011 , 0)
INTO temp (weather_date, weather) VALUES ( 6-jan-2011 , 0)
SELECT * from dual;

The following query returns the results you want based on the above dataset. Change the date offests as needed in the JOIN clause.

SELECT
    DISTINCT a.weather_date
FROM
    temp a
    JOIN temp b
    ON b.weather_date BETWEEN a.weather_date - 2 AND a.weather_date + 1
WHERE
    a.weather = 1
    AND b.weather = 2;

Answer 3

select distinct date
from table t
where exists (select 1
              from table t2
              where t.weather <> t2.weather
              and julianday(t2.date) between julianday(t1.date - N) 
                                         and julianday(t1.date + M)
             )

页: 1 页: 1 ......

Answer 4

SELECT d FROM weather WHERE d IN (
    SELECT date(d,  +1 day ) FROM weather WHERE w=1
    UNION
    SELECT date(d,  +2 day ) FROM weather WHERE w=1
) AND w IN (1, 2)

友情链接