要求用SQL写出
1、创建表
CREATE TABLE `login` (
`USERID` varchar(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`INTIME` datetime NULL DEFAULT NULL,
`ONTIME` datetime NULL DEFAULT NULL,
PRIMARY KEY (`USERID`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
INSERT INTO `login` VALUES ('001', '2023-02-28 13:59:51', '2023-02-28 14:13:51');
INSERT INTO `login` VALUES ('002', '2023-02-28 14:12:48', '2023-02-28 14:20:48');
INSERT INTO `login` VALUES ('003', '2023-02-28 14:18:37', '2023-02-28 14:21:35');
INSERT INTO `login` VALUES ('004', '2023-02-28 14:20:30', '2023-02-28 14:24:36');
INSERT INTO `login` VALUES ('005', '2023-02-28 14:22:32', '2023-02-28 14:26:40');
INSERT INTO `login` VALUES ('006', '2023-02-28 14:23:44', '2023-02-28 14:24:58');
2、解决办法:
SELECT
date( intime ) AS login_date,
max( online_user_cnt ) AS online_user_cnt_max
FROM
(
SELECT
userid,
intime,
sum( index1 ) over ( ORDER BY intime ASC ) AS online_user_cnt
FROM
( SELECT userid, intime, 1 AS index1 FROM login UNION ALL SELECT userid, ontime, - 1 AS index1 FROM login ) a
) b
GROUP BY
date( intime )
解释一下这个SQL语句:
从login表中选择userid和intime字段,并将intime字段转换为日期格式,命名为login_date。
通过子查询,将login表中的userid和intime字段与-1(代表下线)组成一张表a,再将其与1(代表上线)组成一张表b,并对表b按照intime字段升序排序,计算出每个时刻在线用户数,命名为online_user_cnt。
在表b中按照日期分组,计算每天在线用户数的最大值,命名为online_user_cnt_max。
最终结果为每天的日期和该日期的最大在线用户数。
因此,这个SQL语句将返回最大在线用户数。
3、显示结果
拓展:查出intime 和ontime的时间间隔
SELECT USERID, INTIME,ONTIME,TIMESTAMPDIFF(MINUTE, INTIME, ONTIME) AS 两时间段间隔分钟
FROM login;
解释:
MySQL的TIMESTAMPDIFF函数来计算两个时间之间的差值,单位可以是秒、分钟、小时等。
显示结果: