mysql面试题-01

- 日理万妓 2022-05-25 11:15 145阅读 0赞

![2018051322002557][]

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

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;

运行：

![20180513222419817][]

[2018051322002557]: /images/20220525/6932d3a590f644bca826686453272e94.png
[20180513222419817]: /images/20220525/5b075b4e31304fcfb30e8dae5620c764.png