一个有趣的SQL：根据登录日志，求系统无人登录时间

最新推荐文章于 2023-04-20 14:41:58 发布

原创最新推荐文章于 2023-04-20 14:41:58 发布 · 1.5k 阅读

2 ·

CC 4.0 BY-SA版权

方法、思维同时被 2 个专栏收录

6 篇文章

订阅专栏

数据

5 篇文章

订阅专栏

本文详细解析了MySQL中设置外键检查、创建测试表、插入数据及执行复杂SQL查询的过程，包括时间戳操作和多条件分组查询，旨在帮助开发者理解和应用MySQL中的高级功能。

今天MySQL群里有人发了这个问题：

建立测试数据和样表：

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `adtimelog`
-- ----------------------------
DROP TABLE IF EXISTS `adtimelog`;
CREATE TABLE `adtimelog` (
  `Aname` varchar(12) CHARACTER SET utf8 DEFAULT NULL,
  `LITime` timestamp NULL DEFAULT NULL,
  `LOTime` timestamp NULL DEFAULT NULL,
  `DTime` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of adtimelog
-- ----------------------------
INSERT INTO `adtimelog` VALUES ('甲', '2015-08-13 08:30:00', '2015-08-13 09:30:00', '2015-08-13 00:00:00');
INSERT INTO `adtimelog` VALUES ('甲', '2015-08-13 09:40:00', '2015-08-13 10:00:00', '2015-08-13 00:00:00');
INSERT INTO `adtimelog` VALUES ('甲', '2015-08-13 08:30:00', '2015-08-13 11:30:00', '2015-08-13 00:00:00');
INSERT INTO `adtimelog` VALUES ('丁', '2015-08-13 00:30:00', '2015-08-13 01:30:00', '2015-08-13 00:00:00');
INSERT INTO `adtimelog` VALUES ('丁', '2015-08-13 02:30:00', '2015-08-13 07:30:00', '2015-08-13 00:00:00');

SQL：

select '2015-08-13 00:00:00',min(LITime) from ADTimeLog where DTime='2015/8/13' 
 UNION ALL
select distinct a.LOTime,min(b.LITime) from 
(select LITime,LOTime from ADTimeLog where DTime='2015/8/13' order by LITime ) a,
(select LITime,LOTime from ADTimeLog where DTime='2015/8/13' order by LITime limit 1,99999) b
where a.LOTime<b.LITime GROUP BY a.LOTime
 UNION ALL
select max(LOTime),'2015-08-13 23:59:59' from ADTimeLog where DTime='2015/8/13'