SQL查询面试题【中】

糖逗

参考链接：https://mp.weixin.qq.com/s?__biz ... =21#wechat_redirect

#1.要求在剔除访问次数前20%的用户后，每类用户的平均访问次数。
USE train;
#建表
DROP TABLE IF EXISTS 访问;
CREATE TABLE 访问(
        用户编号 INT,
        用户类型 VARCHAR(255),
        访问量 INT
);
INSERT INTO 访问
VALUES(10, 'A', 352),
        (6, 'C', 209),
        (7, 'C', 110),
        (4, 'E', 101),
        (2, 'B', 53),
        (20, 'A', 53),
        (11, 'C', 33),
        (1, 'A', 30),
        (9, 'E', 29),
        (8, 'B', 6);

#剔除
DROP TABLE IF EXISTS temp;
CREATE TABLE temp AS
SELECT *, ROW_NUMBER() OVER(ORDER BY 访问量 DESC) AS ranking
FROM 访问;
#查询
SELECT 用户类型, AVG(访问量) AS 平均访问次数 
FROM temp
WHERE ranking > (SELECT MAX(ranking) FROM temp) * 0.2
GROUP BY 用户类型;


#2.查询每门课程的前3高成绩
#建表
DROP TABLE IF EXISTS 原始表;
CREATE TABLE 原始表(
        课程号 INT,
        学号 INT,
        成绩 INT
);
INSERT INTO 原始表
VALUES(0001, 0001, 80),
        (0001, 0003, 80),
        (0002, 0001, 90),
        (0002, 0003, 80),
        (0002, 0002, 60),
        (0002, 0004, 55),
        (0003, 0001, 99),
        (0003, 0002, 80),
        (0003, 0003, 80);


SELECT 课程号,学号,成绩, 排名
FROM (
        SELECT *, DENSE_RANK() OVER(PARTITION BY 课程号 ORDER BY 成绩 DESC) AS 排名
        FROM 原始表

) AS temp
WHERE 排名 <= 3


#3.查找出每门课程中成绩最好的学生的姓名和该学生的课程及成绩。
DROP TABLE IF EXISTS 学生表;
CREATE TABLE 学生表(
        学号 VARCHAR(255) PRIMARY KEY,
        姓名 VARCHAR(255) NOT NULL,
        出生日期 DATE NOT NULL,
        性别 VARCHAR(255) NOT NULL
);
DESC 学生表;

DROP TABLE IF EXISTS 成绩表;
CREATE TABLE 成绩表(
        学号 VARCHAR(255),
        课程号 VARCHAR(255),
        成绩 FLOAT(3) NOT NULL,
        PRIMARY KEY(学号, 课程号)
);
DESC 成绩表;

DROP TABLE IF EXISTS 课程表;
CREATE TABLE 课程表(
        课程号 VARCHAR(255) PRIMARY KEY,
        课程名称 VARCHAR(255) NOT NULL,
        教师号 VARCHAR(255) NOT NULL
);
DESC 课程表;

#向表中添加数据
INSERT INTO 学生表
VALUES('0001' , '猴子' , '1989-01-01' , '男'),
('0002' , '猴子' , '1990-12-21' , '女'),
('0003' , '马云' , '1991-12-21' , '男'),
('0004' , '王思聪' , '1990-05-20' , '男');

INSERT INTO 成绩表
VALUES('0001' , '0001' , 87),
('0001' , '0002' , 45),
('0001' , '0003' , 45),
('0002' , '0002' , 99),
('0002' , '0003' , 98),
('0003' , '0001' , 88),
('0003' , '0002' , 80),
('0003' , '0003' , 80);
('0003' , '0003' , 23);


INSERT INTO 课程表
VALUES('0001' , '语文' , '0002'),
('0002' , '数学' , '0001'),
('0003' , '英语' , '0003');
#查询
SELECT c.学号, c.姓名, b.课程名称, a.成绩
FROM 成绩表 a
        LEFT JOIN 课程表 b ON a.课程号 = b.课程号
        LEFT JOIN 学生表 c ON a.学号 = c.学号
WHERE (a.课程号, a.成绩) IN
(SELECT 课程号,MAX(成绩) FROM 成绩表 GROUP BY 课程号);

#4.生成一张临时表（表名：用户登录表），表中呈现四列数据分别为：姓名，最后登录时间，登录时间排名，登录天数排名
#建表
DROP TABLE IF EXISTS 用户登记时间表;
CREATE TABLE 用户登记时间表(
        id INT PRIMARY KEY,
        姓名 VARCHAR(255),
        邮箱地址 VARCHAR(255),
        最后登录时间 DATETIME 
);
INSERT INTO 用户登记时间表
VALUES (100, 'test4', 'test4@yahoo.cn', '2007/11/25 16:31:00'),
        (13, 'test1', 'test4@yahoo.cn', '2007/03/22 16:27:00'),
        (19, 'test1', 'test4@yahoo.cn', '2007/10/25 14:13:00'),
        (42, 'test1', 'test4@yahoo.cn', '2007/10/25 14:20:00'),
        (45, 'test2', 'test4@yahoo.cn', '2007/04/25 14:17:00'),
        (49, 'test2', 'test4@yahoo.cn', '2007/05/25 14:22:00');
#查询
SELECT 姓名,最后登录时间,
       ROW_NUMBER() OVER (PARTITION BY 姓名
                          ORDER BY 最后登录时间 ASC) AS 登录时间排名,
       DENSE_RANK() OVER (PARTITION BY 姓名
                          ORDER BY DATE_FORMAT( 最后登录时间,'%Y%m&d' ) ASC) AS 登录天数排名
FROM  用户登记时间表 ;

#5.找出每门课程中成绩最差的学生
DROP TABLE IF EXISTS 成绩表;
CREATE TABLE 成绩表(
        学号 INT,
        课程号 INT,
        成绩 INT
);

INSERT INTO 成绩表
VALUES('0001' , '0001' , 80),
        ('0001' , '0002' , 90),
        ('0001' , '0003' , 99),
        ('0002' , '0002' , 60),
        ('0002' , '0003' , 80),
        ('0003' , '0001' , 80),
        ('0003' , '0002' , 80),
        ('0003' , '0003' , 80);

#查询
SELECT *
FROM 成绩表
WHERE (课程号, 成绩) IN (
        SELECT 课程号, MIN(成绩) AS 成绩
        FROM 成绩表
        GROUP BY 课程号
);