题解 | SQL 186. 牛客直播开始时各直播间在线人数

WITH t1 AS (
    SELECT user_id,
           course_id,
           TIME(in_datetime),
           out_datetime
    FROM attend_tb
    WHERE TIME(in_datetime) <= '19:00:00'
          AND  '19:00:00' <= TIME(out_datetime)
),
t2 AS (
    SELECT a.course_id,
           a.course_name,
           count(distinct user_id) AS online_num
    FROM course_tb a
             LEFT JOIN t1
                       ON a.course_id = t1.course_id
    GROUP BY a.course_id, a.course_name
    ORDER BY course_id ASC
)
SELECT * FROM t2;
           TIME(in_datetime),
           out_datetime
    FROM attend_tb
    WHERE TIME(in_datetime) <= '19:00:00'
          AND  '19:00:00' <= TIME(out_datetime)
),
t2 AS (
    SELECT a.course_id,
           a.course_name,
           count(distinct user_id) AS online_num
    FROM course_tb a
             LEFT JOIN t1
                       ON a.course_id = t1.course_id
    GROUP BY a.course_id, a.course_name
    ORDER BY course_id ASC
)
SELECT * FROM t2;
            user_id,
            course_id,
            TIME(in_datetime),
            out_datetime
        FROM
            attend_tb
        WHERE
            TIME(in_datetime) <= '19:00:00'
            AND '19:00:00' <= TIME(out_datetime)
    ),
    t2 AS (
        SELECT
            a.course_id,
            a.course_name,
            count(distinct user_id) AS online_num
        FROM
            course_tb a
            LEFT JOIN t1 ON a.course_id = t1.course_id
        GROUP BY
            a.course_id,
            a.course_name
        ORDER BY
            course_id ASC
    )
SELECT
    *
FROM
    t2;

# 判断逻辑需要主意：where 需要分段判断时，需要用 and 进行连接 ！