连续三个时间段

CREATE TABLE stadium(
			id     INT    NOT NULL,
            date   DATE   NOT NULL,
            people INT    NOT NULL);

INSERT INTO stadium VALUES(1,'2007-01-01',10);
INSERT INTO stadium VALUES(2,'2007-01-02',109);
INSERT INTO stadium VALUES(3,'2007-01-03',150);
INSERT INTO stadium VALUES(4,'2007-01-04',99);
INSERT INTO stadium VALUES(5,'2007-01-05',145);
INSERT INTO stadium VALUES(6,'2007-01-06',1455);
INSERT INTO stadium VALUES(7,'2007-01-07',199);
INSERT INTO stadium VALUES(8,'2007-01-08',188);



select  a.* from stadium a,stadium b,stadium c
where a.people>=100 and b.people>=100 and c.people>=100
and (
     (a.id = b.id-1 and b.id = c.id -1) or
     (a.id = b.id-1 and a.id = c.id +1) or
     (a.id = b.id+1 and b.id = c.id +1)
) order by a.id
;

这里当有三个以上的时候中间的会重复，所以要加上distinct

select  DISTINCT a.* from stadium a,stadium b,stadium c
where a.people>=100 and b.people>=100 and c.people>=100
and (
     (a.id = b.id-1 and b.id = c.id -1) or
     (a.id = b.id-1 and a.id = c.id +1) or
     (a.id = b.id+1 and b.id = c.id +1)
) order by a.id
;

 

这个是别人想的，自己还需要反复回顾

就是关键是要想通3个以上！

 

select  DISTINCT a.* from stadium a,stadium b,stadium c
where a.people>=100 and b.people>=100 and c.people>=100
and (
     (a.date = DATE_SUB(b.date,INTERVAL 1 DAY) and b.date = DATE_SUB(c.date,INTERVAL 1 DAY)) or
     (a.date = DATE_SUB(b.date,INTERVAL 1 DAY) and a.date = DATE_SUB(c.date,INTERVAL -1 DAY)) or
     (a.date = DATE_SUB(b.date,INTERVAL -1 DAY) and b.date = DATE_SUB(c.date,INTERVAL -1 DAY))
) order by a.date
;

 

特别注意and（（A and B） OR

                         （C and D））

DROP TABLE Logs1;
CREATE TABLE Logs1(
			id     INT    NOT NULL,
            Num INT    NOT NULL);


INSERT INTO Logs1 VALUES(1,1);
INSERT INTO Logs1 VALUES(2,1);
INSERT INTO Logs1 VALUES(3,1);
INSERT INTO Logs1 VALUES(4,2);
INSERT INTO Logs1 VALUES(5,1);
INSERT INTO Logs1 VALUES(6,2);
INSERT INTO Logs1 VALUES(7,2);
INSERT INTO Logs1 VALUES(8,3);
INSERT INTO Logs1 VALUES(9,3);
INSERT INTO Logs1 VALUES(10,3);
INSERT INTO Logs1 VALUES(11,3);

法一

SELECT DISTINCT l1.Num AS 'ConsecutiveNums'
  FROM Logs1 l1,Logs1 l2,Logs1 l3
 WHERE  l1.Num = l2.Num AND l3.Num = l2.Num
   AND    ((l1.id = l2.id -1 AND l1.id = l3.id-2)
            OR (l1.id = l2.id +1 AND l1.id = l3.id-1)
            OR (l1.id = l2.id +1 AND l2.id = l3.id+1));

 法二

SELECT DISTINCT l1.Num AS 'ConsecutiveNums'
  FROM Logs1 l1
  JOIN Logs1 l2
    ON l1.id = l2.id-1
   AND l1.Num = l2.Num
  JOIN Logs1 l3
    ON l1.id = l3.id-2
   AND l2.Num = l3.Num;

可以用法二是因为我只要求num没要其他的东西，

如果像例子1那样，就只能用法1

SELECT DISTINCT l1.* 
  FROM Logs1 l1,Logs1 l2,Logs1 l3
 WHERE  l1.Num = l2.Num AND l3.Num = l2.Num
   AND    ((l1.id = l2.id -1 AND l1.id = l3.id-2)
            OR (l1.id = l2.id +1 AND l1.id = l3.id-1)
            OR (l1.id = l2.id +1 AND l2.id = l3.id+1));

 

能不能用窗口函数实现？如果是连续3个时间段，说明向前移步2个之后，会使两者会有相差2的情况，这个题更特殊的地方在于还需要判断连续3个时间段的people人数，所以需要写的窗口函数多一点

select
	id,date
from
	(
	select
		id
		,date
		,people
		,lag(date,1) over(order by date) as lag1_date
		,lag(date,2) over(order by date) as lag2_date
		,lag(people,1) over(order by date) as lag1_people
		,lag(people,2) over(order by date) as lag2_people
	from statium
	) as t
where t.people >=100 and lag1_people >=100 and lag2_people >=100
and datediff(t.date,lag2_date) = 2

但是这个情况下只是取出一个id，我们也需要之前的数据，需要再嵌套一层

select
	distinct id,date,people
from statium
where id
in 
	(
	select
		id
	from
		(
		select
			id
			,date
			,people
			,lag(date,1) over(order by date) as lag1_date
			,lag(date,2) over(order by date) as lag2_date
			,lag(people,1) over(order by date) as lag1_people
			,lag(people,2) over(order by date) as lag2_people
		from statium
		) as t
	where t.people >=100 and lag1_people >=100 and lag2_people >=100
	and datediff(t.date,lag2_date) = 2
)