嗯@@...這篇可以玩玩XD

MSSQL

declare @Tmp table(
	id int
	,[date] date
	,people int
)

insert into @Tmp
values(1,'2017-01-01',10)
,(2,'2017-01-02',109)
,(3,'2017-01-03',150)
,(4,'2017-01-04',99)
,(5,'2017-01-05',145)
,(6,'2017-01-06',1455)
,(7,'2017-01-07',199)
,(8,'2017-01-08',188)

select * 
from @Tmp as a
where (
	select min(people)
	from @Tmp as b
	where a.id <= b.id
	and a.id between a.id and a.id + 2

) >= 100

MSSQL連結

MySQL

Create table stadium (id int, dates DATE NULL, people int);

insert into stadium(id, dates, people) 
values ('1', '2017-01-01', '10')
,('2', '2017-01-02', '109')
,('3', '2017-01-03', '150')
,('4', '2017-01-04', '99')
,('5', '2017-01-05', '145')
,('6', '2017-01-06', '1455')
,('7', '2017-01-07', '199')
,('8', '2017-01-08', '188');

select * 
from stadium as a
where (
	select min(people)
	from stadium as b
	where a.id <= b.id
	and a.id between a.id and a.id + 2

) >= 100

MySQL連結

用一個特別的方式來做

select T1.*
from stadium T1,(select @strC := (select CONCAT(GROUP_CONCAT(id) ,',')
                  from stadium
                  where people >= 100)) T2
where people >= 100
and (
  @strC REGEXP concat(
    CONCAT(id-2,',',id-1,',',id,',')  
    ,'|',CONCAT(id-1,',',id,',',id+1,',') 
    ,'|',CONCAT(id,',',id+1,',',id+2,',') 
  )
) ;

1.先把100人數以上的資料以字串變數(使用GROUP_CONCAT把每個id都以,相加方便regex like)保存起來(@strC)
2.用regex like |(or)方式，找出連續三次的資料
3.只有以下三種情況都有資料那麼是連續三次的資料
id-2,id-1,id 或是 id-1,id,id+1 或是 id,id+1,id+2

SQL Fiddle Demo Link

使用cross join宣告變數是因為leetcode的限制，可以拿掉放最前面宣告，效能也比較好。

##set varible
set @strC   := (select CONCAT(GROUP_CONCAT(id) ,',')
                from stadium
                where people >= 100);

##query using rlike
select T1.*
from stadium T1
where people >= 100
and (
  @strC   Rlike 
  concat(
    CONCAT(id,',',id+1,',',id+2,',') 
    ,'|',CONCAT(id-2,',',id-1,',',id,',')  
    ,'|',CONCAT(id-1,',',id,',',id+1,',') 
  )
) ;

SQL Fiddle Demo Link

既然是日期連續三天,我就把測試資料變動一下,id就跳動,以日期來作判斷.

create table ithelp180702 (
  id serial primary key
, idate date not null
, people int not null
);

insert into ithelp180702 (idate, people) values
('2017-01-05'::date, 145),
('2017-01-06'::date, 1455),
('2017-01-08'::date, 188),
('2017-01-03'::date, 150),
('2017-01-04'::date, 99),
('2017-01-01'::date, 10),
('2017-01-02'::date, 109),
('2017-01-07'::date, 199);

 id |   idate    | people 
----+------------+--------
  1 | 2017-01-05 |    145
  2 | 2017-01-06 |   1455
  3 | 2017-01-08 |    188
  4 | 2017-01-03 |    150
  5 | 2017-01-04 |     99
  6 | 2017-01-01 |     10
  7 | 2017-01-02 |    109
  8 | 2017-01-07 |    199
(8 筆資料列)
---

with t1 as (
select idate
     , lag(idate, 1) over w l1
     , lag(idate, 2) over w l2
  from ithelp180702
 where people >= 100
window w as (order by idate)
), t2 as (
select array[idate, l1, l2] datearr
  from t1
 where idate - l2 = 2
), t3 as (
select distinct unnest(datearr) pickdate
  from t2
)
select a.*
  from t3
  join ithelp180702 a
    on t3.pickdate = a.idate
 order by a.idate
;

 id |   idate    | people 
----+------------+--------
  1 | 2017-01-05 |    145
  2 | 2017-01-06 |   1455
  8 | 2017-01-07 |    199
  3 | 2017-01-08 |    188
(4 筆資料列)

使用了 window function lag(),還有array function unnest().

這次的題目也是熟悉的 連續範圍 問題，不過題目限定 MySql 沒有 CTE 可以用，寫法有點笨拙，懷念的 CTE。

select id, date, people from (
    select g 
    from (
        /* 將人數大於等於100的資料重新編號，
           再用id減去新編號，就是連續資料的群組編號 */
        select t.*, id-(@i1:=@i1+1) as g
        from (
            select * from stadium where people>=100
        ) as t, (select @i1:=1) as temp
    ) as t
    group by g
    /* 找出大於等於3天的群組 */
    having count(id) >= 3
) as a
inner join (
    /* 以群組編號 join 回原資料，就是期望的結果 */
    select t.*, id-(@i2:=@i2+1) as g
    from (
        select * from stadium where people>=100
    ) as t, (select @i2:=1) as temp
) as b on b.g=a.g
order by id

SQL 的分數有點飄，我絕對不會承認，我按了好多次選了一個最高的。

MySQL連結

幫大大測試了 OR 的邏輯，OR 只要遇到 true 就會返回，和程式語言的 || 相同。

select @i as i,
    case when (1=1 or (@i:=@i+1)>0) then 1 else 0 end as x
from (
    select * from temp
) as t, (select @i:=1) as temp

MySQL連結

MSSQL 版本，用 CTE 和 ROW_NUMBER 簡潔很多。

;WITH CTE AS
(
	SELECT *, id - ROW_NUMBER() OVER (ORDER BY id) AS g
	FROM @Temp WHERE people>=100
)

SELECT id, [date], people
FROM
(
	SELECT g
	FROM CTE
	GROUP BY g
	HAVING COUNT(id)>=3
) AS A
INNER JOIN CTE AS B ON B.g=A.g
ORDER BY id

MSSQL連結