[演算法][SQL]演算法挑战系列(9)-Human Traffic of Stadium

嗨啊！大家好！这礼拜有点忙，所以在星期日的尾巴才有时间PO出这篇文，绝对不是因为我偷懒想晃掉这个礼拜XD，那一样，说好的每五篇就一个Hard的题目(谁跟你说好XD)，这礼拜也轮到了！其实这一篇解题的逻辑不难，因为之前有经历过换座位的题目，感觉还满类似的，所以如果卡关了可以回顾一下！他在这里：[演算法][SQL]演算法挑战系列(7)-Exchange Seats，好到这边就好，打广告不能太明显XD，那让我们来看看题目是什么吧！

题目：Human Traffic of Stadium
难易度：高
题目内容：事情是这样子，在Ｑ市，神Ｑ超人他建了一栋体育馆。经营一阵子后，他想查询连续三天，体育馆的入场人数都大于等于100的资料，该怎么做呢？
例如：
stadium表内容：

iddatepeople12017-01-011022017-01-0210932017-01-0315042017-01-049952017-01-0514562017-01-06145572017-01-0719982017-01-08188查询结果：iddatepeople---------------------------------------52017-01-0514562017-01-06145572017-01-0719982017-01-08188就是这样子，不管是前后两天，或是前一天后一天，只要连续三天都有100人以上浏览，就把该笔资料列出来，另外贴心小提醒，表中的id栏位会和date一样依序编号下去，不会有跳号的情形，那以下来看一下解法吧！

SELECT *FROM stadium mWHERE m.people >=100 AND/*找出明后两天的资料，然后条件人数大于100的资料，因为是明后两天所以如果数量等于2才算*/CASE WHEN (SELECT COUNT(*) _count FROM stadium d WHERE (d.id = m.id + 1 OR d.id = m.id + 2) AND d.people >= 100) = 2 THEN TRUE /*找出明天和昨天的资料，然后条件人数大于100的资料，因为是两天所以如果数量等于2才算*/WHEN (SELECT COUNT(*) _count FROM stadium d WHERE (d.id = m.id + 1 OR d.id = m.id - 1) AND d.people >= 100) = 2 THEN TRUE/*找出昨天和前天的资料，然后条件人数大于100的资料，因为是两天所以如果数量等于2才算*/WHEN (SELECT COUNT(*) _count FROM stadium d WHERE (d.id = m.id - 1 OR d.id = m.id - 2) AND d.people >= 100) = 2 THEN TRUE/*如果都没有就回传FALSE不显示*/ELSE FALSE END

以上那样解是因为id不会跳号，所以才这样直接用加减1,2来取他附近的日期，然后会让人觉得奇怪的地方可能是为什么要用CASE WHEN，这部分也是我想要请教版上大大的地方，其实我一开始是写OR去连接那三种情况的条件，就像平常一样，但是跑出来的效率差到不行，原因也很明显是子查询的关係，后来就在想如果是(id='A' OR id='B')这种情况，是会判断完两个条件后再回传TRUE或是判断到id='A'就不再判断id='B'了，想请版上大大解答一下这个疑惑。

这方面，小弟认为是都会判断！所以为了让成绩好看一点就改成了CASE WHEN id = 'A' THEN TRUE WHEN id = 'B' THEN TRUE ELSE FALSE，让他可以判断到id='A'就回传TRUE不会再跑下一个WHEN，想说这样效能或许会好一点，那也来分享一下这次的成绩：

我也找了一些其他大大讨论的解答，题外话我居然有荣幸看到和我一样的解答XD，不过是OR版本的就不再分享，我们就来看看其他写法吧：
原解答网址

/*(6)最后一步因为(4)的INNER JOIN会找出重複的资料，    所以用DISTINCT过滤掉。*/SELECT DISTINCT c.*FROM stadium cINNER JOIN(/*(3)在这里用MIN()来找出昨天、今天、明天中最少的人数  另外也用COUNT()来判断说该笔资料出现的次数(最后的WHERE再说明用途)*/SELECT a.id id, MIN(b.people) min_people, COUNT(1) units_aroundFROM stadium aINNER JOIN stadium b/*(1)用a.id-b.id在-1到1之间去INNER JOIN找出前后1天的资料*/ON a.id - b.id BETWEEN -1 AND 1/*(2)把同个id做群组GROUP BY起来*/GROUP BY a.id) d/*(4)比较关键的地方在这边，    他在这里用里面查出来的资料再JOIN一次，原本资料表的资料，    这时候会找出原本的所有日期对应出的当天＋前后一天的最少人数的资料，*/ON c.id - d.id BETWEEN -1 AND 1/*(5)因为(4)的关係，所以在这里判断该日期＋前后的三天资料有没有一笔大于100    第二个条件是因为(3)的COUNT(1) units_around其实就像是比较几笔资料，    而因为题目中要连续3天大于100，所以这边要等于3*/WHERE d.min_people >= 100 and d.units_around = 3

上面的方法要理解起来满困难的(对我来说啦XD)，所以上面解释的方式可能有点不清楚，尤其是第(5)步骤的地方，也是去查了以后才明白它的道理，所以如果以上解释不清楚的话，可以再告诉我！我再想想怎么表达会比较好，啊如果是表达错误的地方，也麻烦大大们提出，我会儘速修正！那以下会提供CREATE的SQL方便大家练习，啊我一直忘了说，这一次官方的解题方式也只有提供MySQL的方式，不过文章内的两种解法在MSSQL上也不会有问题的！

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

最后的最后，还是感谢大家的观看！现在也不能祝大家週末愉快了(其实我也没这么说过XD)，就祝大家新一个礼拜继续好好加油吧！谢谢大家！