601. Human Traffic of Stadium 体育馆的人流量_human traffic of stadium 窗口函数-优快云博客

本文探讨了三种不同的SQL查询策略，用于检索连续三天观众人数至少为100人的体育场记录。通过对比不同方法的执行时间，展示了如何优化SQL查询，提高数据检索效率。

题目

代码部分一（545ms）

SELECT
    s.*
    FROM(
    SELECT
        s1.*
        FROM stadium s1
        JOIN stadium s2 ON s2.id=s1.id+1
        JOIN stadium s3 ON s3.id=s2.id+1
        WHERE s1.people>=100 AND s2.people>=100 AND s3.people>=100
    UNION
    SELECT
        s2.*
        FROM stadium s1
        JOIN stadium s2 ON s2.id=s1.id+1
        JOIN stadium s3 ON s3.id=s2.id+1
        WHERE s1.people>=100 AND s2.people>=100 AND s3.people>=100
    UNION
    SELECT
        s3.*
        FROM stadium s1
        JOIN stadium s2 ON s2.id=s1.id+1
        JOIN stadium s3 ON s3.id=s2.id+1
        WHERE s1.people>=100 AND s2.people>=100 AND s3.people>=100
    ) s ORDER BY s.id;

代码部分二（486ms）

SELECT
    s.*
    FROM(
    SELECT
        s1.*
        FROM stadium s1,stadium s2,stadium s3 
        WHERE s2.id=s1.id+1 AND s3.id=s2.id+1 AND s1.people>=100 AND s2.people>=100 AND s3.people>=100
    UNION
    SELECT
        s2.*
        FROM stadium s1,stadium s2,stadium s3 
        WHERE s2.id=s1.id+1 AND s3.id=s2.id+1 AND s1.people>=100 AND s2.people>=100 AND s3.people>=100
    UNION
    SELECT
        s3.*
       FROM stadium s1,stadium s2,stadium s3 
        WHERE s2.id=s1.id+1 AND s3.id=s2.id+1 AND s1.people>=100 AND s2.people>=100 AND s3.people>=100
    ) AS s ORDER BY s.id;

代码部分三（410ms）

SELECT
    DISTINCT s1.id,s1.date,s1.people
    FROM stadium s1,stadium s2,stadium s3
    WHERE s1.people>=100 AND s2.people>=100 AND s3.people>=100
    AND(
        (s1.id+1=s2.id AND s1.id+2=s3.id)
        OR (s1.id-1=s2.id AND s1.id+1=s3.id)
        OR (s1.id-2=s2.id AND s1.id-1=s3.id)
    )
    ORDER BY s1.id;