SQL题目3：计算访问量最高的时间和访问量

WITH T1 AS  -- 自己领悟什么是阴阳变化
 (SELECT LOG_IN AS LOG_TIME, 1 AS FLAG
    FROM LOG_TABLE
  UNION ALL
  SELECT LOG_OUT AS LOG_TIME, -1 AS FLAG FROM LOG_TABLE),
T2 AS      -- 有阴又有阳 直接就冲气以为和了
 (SELECT T.LOG_TIME, FLAG, SUM(FLAG) OVER(ORDER BY LOG_TIME ASC) AS SUM_FLAG
    FROM T1 T)
SELECT DISTINCT LOG_TIME, SUM_FLAG   -- 这就是show time 了
  FROM T2
 WHERE SUM_FLAG = (SELECT MAX(SUM_FLAG) FROM T2)


复制