mysql面试题-01

最新推荐文章于 2023-03-08 09:35:04 发布

原创最新推荐文章于 2023-03-08 09:35:04 发布 · 384 阅读

1 ·

CC 4.0 BY-SA版权

问题反馈专栏收录该内容

7 篇文章

订阅专栏

我理解的答案（有问题请提出谢谢）：

DROP DATABASE IF EXISTS Test;
CREATE DATABASE Test CHARACTER SET utf8;

USE Test;

DROP TABLE IF EXISTS SysLog;
CREATE TABLE SysLog(
	UserId INT,
	`time` DATETIME
);

DROP TABLE IF EXISTS SysUser;
CREATE TABLE SysUser(
	UserId INT PRIMARY KEY AUTO_INCREMENT,
	`Name` VARCHAR(20)
);

INSERT INTO SysLog VALUES(1,'2011-01-26');
INSERT INTO SysLog VALUES(2,'2011-05-27');
INSERT INTO SysLog VALUES(1,'2011-06-01');

INSERT INTO SysUser VALUES(NULL,'张三');
INSERT INTO SysUser VALUES(NULL,'李四');

SELECT * FROM SysUser;
SELECT * FROM SysLog;



SELECT sl.UserId AS '编号',
sl.`Name` AS '名称',
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND YEAR(`time`)=2011) AS '登录次数',
(SELECT COUNT(*) FROM (SELECT * FROM SysLog GROUP BY DAY(`time`)) a  WHERE a.UserId=sl.UserId) AS '登录天数',
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND MONTH(`time`)=1 AND YEAR(`time`)=2011) AS '一月登录次数',
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND MONTH(`time`)=2 AND YEAR(`time`)=2011) AS '二月登录次数',
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND MONTH(`time`)=3 AND YEAR(`time`)=2011) AS '三月登录次数',
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND MONTH(`time`)=4 AND YEAR(`time`)=2011) AS '四月登录次数',
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND MONTH(`time`)=5 AND YEAR(`time`)=2011) AS '五月登录次数',
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND MONTH(`time`)=6 AND YEAR(`time`)=2011) AS '六月登录次数',
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND MONTH(`time`)=7 AND YEAR(`time`)=2011) AS '七月登录次数',
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND MONTH(`time`)=8 AND YEAR(`time`)=2011) AS '八月登录次数',
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND MONTH(`time`)=9 AND YEAR(`time`)=2011) AS '九月登录次数',
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND MONTH(`time`)=10 AND YEAR(`time`)=2011) AS '十月登录次数',
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND MONTH(`time`)=11 AND YEAR(`time`)=2011) AS '十一月登录次数',
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND MONTH(`time`)=12 AND YEAR(`time`)=2011) AS '十二月登录次数'
FROM SysUser sl WHERE
(SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND YEAR(`time`)=2011)>=2 
GROUP BY sl.UserId
ORDER BY (SELECT COUNT(*) FROM SysLog WHERE SysLog.UserId=sl.UserId AND YEAR(`time`)=2011) DESC;

运行：