1.设计表并完成下列需求
部门表 dept
员工表 emp
# 创建部门表dept
CREATE TABLE dept(
deptno int primary key auto_increment COMMENT '部门编号',
dname VARCHAR(20) COMMENT '部门名称',
loc VARCHAR(13) COMMENT '部门地点'
);
# 插入数据
INSERT INTO dept VALUES(null,'accounting','一区');
INSERT INTO dept VALUES(null,'research','二区');
INSERT INTO dept VALUES(null,'operations','二区');
# 创建员工表emp
CREATE TABLE emp(
empno int primary key auto_increment COMMENT '员工编号',
ename VARCHAR(10) COMMENT '员工姓名',
job VARCHAR(10) COMMENT '职位',
mgr int COMMENT '上级编号',
hiredate DATE COMMENT '入职时间',
sal double COMMENT '月薪',
comm NUMERIC(7,2) COMMENT '奖金',
deptno int COMMENT '所属部门'
);
INSERT INTO emp VALUES(100,'Alice','副总',NULL,'2002-05-1',90000,NULL,2);
INSERT INTO emp VALUES(200,'Ben','总监',100,'2015-02-02',40000,3000,1);
INSERT INTO emp VALUES(300,'Ben','CEO',100,'2016-02-02',30000,2000,1);
INSERT INTO emp VALUES(400,'Jone','经理',300,'2017-02-02',25000,1000,3);
INSERT INTO emp VALUES(500,'Bella','经理',300,'2019-02-02',20000,1000,3);
INSERT INTO emp VALUES(600,'Evan','员工',400,'2019-02-22',4500,200.12,3);
INSERT INTO emp VALUES(700,'Hedy','员工',500,'2020-03-19',6500,200.58,3);
INSERT INTO emp VALUES(800,'Susan','员工',500,'2023-04-20',12000,300.64,3);条件查询
1). 查询没有奖金的员工信息；
SELECT * FROM emp WHERE comm IS NULL
2). 查询有奖金的员工信息；
SELECT * FROM emp WHERE comm IS NOT NULL3). 查询员工表中工资5000~20000的信息；
SELECT * FROM emp WHERE
#sal>=5000 and sal<20000
sal BETWEEN 5000 AND 20000 4). 查询2023年入职的员工姓名；
SELECT ename FROM emp WHERE
#hiredate between '2023-1-1' and '2023-12-31'
YEAR(hiredate)=20235). 展示前两条员工数据；
SELECT * FROM emp LIMIT 2
SELECT * FROM emp LIMIT 0,2 #从0+1的数据开始,总共展示2条6). 查询岗位是员工的第一条记录；
SELECT * FROM emp WHERE job='员工' LIMIT 0,1#limit通常放最后 7). 按照工资排序（降序）；
SELECT * FROM emp ORDER BY sal DESC #按数值降序8). 按照名字排序（按字母降序）；
SELECT * FROM emp ORDER BY ename DESC #按字母降序9). 查询17年到23年入职的员工信息只取前两条并按照工资升序排序；
SELECT * FROM emp WHERE
YEAR(hiredate) BETWEEN 2017 AND 2023 #区间[2017,2023]
ORDER BY sal #排序,默认的升序
LIMIT 2 #分页,只取前两条,通常放最后统计
10). 统计员工表中都有人的年薪；
SELECT sal,comm,sal*16+IFNULL(comm,0)*16 FROM emp11). 统计2019年以前入职的员工信息；
SELECT * FROM emp WHERE
YEAR(hiredate)<2019聚合函数
12). 统计名字里包含a的总记录数；
SELECT COUNT(1) FROM emp WHERE ename LIKE '%a%'13).统计员工表中的最高薪；
SELECT MAX(sal) FROM emp 14). 统计1号部门的平均薪资；
SELECT AVG(sal) FROM emp WHERE deptno=115). 统计1号部门一年的工资支出总数；
SELECT SUM(sal)*12 FROM emp WHERE deptno=216). 统计每个部门的平均薪资；
SELECT AVG(sal),deptno FROM emp
GROUP BY deptno #按照部门分组17). 统计每个部门的最高薪资；
SELECT MAX(sal),deptno FROM emp
#查询结果中出现了混合列,包括着聚合列和非聚合列,必须分组
GROUP BY deptno18). 统计每年入职的总人数；
SELECT YEAR(hiredate),COUNT(1) FROM emp
GROUP BY YEAR(hiredate)19). 统计每个岗位的平均薪资（保留两位小数）；
SELECT FORMAT(AVG(sal),2),job FROM emp
GROUP BY job20). 统计每个岗位的平均薪资,而且只要>=10000的；
SELECT AVG(sal) a,job FROM emp
#where AVG(sal)>=10000
#注意:::where里不能用别名,也不能出现聚合函数
GROUP BY job #按照非聚合列分组
HAVING a>=10000 #分组后的过滤,a是别名21). 统计每年入职的人数,而且只要19年的。
SELECT YEAR(hiredate) ,COUNT(1) FROM emp
WHERE YEAR(hiredate)=2019
GROUP BY YEAR(hiredate) #分组
#having a=2019 #分组后的过滤
# where比having 高效,因为执行时机要早一些2.返回Customers表中的顾客名称(cust_name)和Orders表中的相关订单号（order_num），添加第三列OrderTotal,其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序
Orders表：订单号（order_num）、顾客id（cust_id）
Customers表：顾客名称（cust_name）、顾客id（cust_id）
OrderItems表：商品订单号（order_num）、商品数量（quantity）、商品价格（item_price）
# 订单信息表Orders
DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');
# 顾客信息表 Customerss
DROP TABLE IF EXISTS `Customerss`;
CREATE TABLE IF NOT EXISTS `Customerss`(
cust_id VARCHAR(255) NOT NULL COMMENT '客户id',
cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customerss` VALUES ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');
# 商品信息表 OrderItems
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
quantity INT(16) NOT NULL COMMENT '商品数量',
item_price INT(16) NOT NULL COMMENT '商品价格'
);
INSERT `OrderItems` VALUES ('a1',1000,10),('a2',200,10),('a3',10,15),('a4',25,50),('a5',15,25),('a7',7,7);
# 需求：返回 Customers 表中的顾客名称（cust_name）和 Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序
select c.cust_name,t.order_num,t.OrderTotal from Orders o
join (select order_num, sum(quantity * item_price) OrderTotal
from OrderItems
group by order_num
) t on t.order_num = o.order_num join Customerss c on c.cust_id = o.cust_id
order by c.cust_name,t.order_num;3.请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分
exam_record表：用户ID（uid）、试卷ID（exam_id）、开始作答时间（start_time）、交卷时间（submit_time）、得分（score）
examination_info表：试卷ID（exam_id）、试卷类别（tag）、试卷难度（difficulty）、考试时长（duration）、发布时间（release_time）
drop table if exists examination_info;
CREATE TABLE examination_info (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
drop table if exists exam_record;
CREATE TABLE exam_record (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO examination_info(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2023-01-01 10:00:00'),
(9002, 'SQL', 'easy', 60, '2023-02-01 10:00:00'),
(9003, '算法', 'medium', 80, '2023-08-02 10:00:00');
INSERT INTO exam_record(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2023-01-02 09:01:01', '2023-01-02 09:21:01', 80),
(1002, 9001, '2023-09-05 19:01:01', '2023-09-05 19:40:01', 89),
(1002, 9002, '2023-09-02 12:01:01', null, null),
(1002, 9003, '2023-09-01 12:01:01', null, null),
(1002, 9001, '2023-02-02 19:01:01', '2023-02-02 19:30:01', 87),
(1002, 9002, '2023-05-05 18:01:01', '2023-05-05 18:59:02', 90),
(1003, 9002, '2023-02-06 12:01:01', null, null),
(1003, 9003, '2023-09-07 10:01:01', '2023-09-07 10:31:01', 86),
(1004, 9003, '2023-09-06 12:01:01', null, null);
# 需求：请从试卷作答记录表中找到SQL试卷得分不小于该类试卷平均得分的用户最低得分。
SELECT MIN(score) min_score_over_avg
FROM exam_record
WHERE exam_id IN (SELECT exam_id FROM examination_info WHERE tag = 'SQL')
AND score >=(
SELECT AVG(score) avg_score
FROM exam_record
WHERE exam_id IN (
SELECT exam_id FROM examination_info WHERE tag = 'SQL'
)
);
select min(e_r.score) as min_score_over_avg
from exam_record e_r join examination_info e_i
on e_r.exam_id = e_i.exam_id
where e_i.tag = 'SQL'
and score >= (select avg(e1.score)
from exam_record e1 join examination_info e2
on e1.exam_id = e2.exam_id
where tag = 'SQL'
)
4.请从表中统计出 “当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出
试卷作答记录表exam_record：用户ID（uid），试卷ID（exam_id），开始作答时间（start_time），交卷时间（submit_time）、没提交的话为NULL，得分（score）
试卷信息表examination_info：试卷ID（exam_id），试卷类别（tag），试卷难度（difficulty），考试时长（duration），发布时间（release_time）
drop table if exists examination_info2,exam_record2;
CREATE TABLE examination_info2 (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE exam_record2 (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO examination_info2(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2023-01-01 10:00:00'),
(9002, 'C++', 'easy', 60, '2023-02-01 10:00:00'),
(9003, '算法', 'medium', 80, '2023-08-02 10:00:00');
INSERT INTO exam_record2(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2023-07-02 09:01:01', null, null),
(1002, 9003, '2023-09-01 12:01:01', '2023-09-01 12:21:01', 60),
(1002, 9002, '2023-09-02 12:01:01', '2023-09-02 12:31:01', 70),
(1002, 9001, '2023-09-05 19:01:01', '2023-09-05 19:40:01', 81),
(1002, 9002, '2023-07-06 12:01:01', null, null),
(1003, 9003, '2023-09-07 10:01:01', '2023-09-07 10:31:01', 86),
(1003, 9003, '2023-09-04 12:01:01', '2023-09-04 12:11:01', 40),
(1003, 9001, '2023-09-04 13:01:01', null, null),
(1003, 9002, '2023-09-04 14:01:01', null, null),
(1003, 9003, '2023-09-04 15:01:01', null, null),
(1005, 9001, '2023-09-01 12:01:01', '2023-09-01 12:31:01', 88),
(1005, 9002, '2023-09-01 12:01:01', '2023-09-01 12:31:01', 88),
(1005, 9002, '2023-09-02 12:11:01', '2023-09-02 12:31:01', 89);
# 需求：请从表中统计出 “当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出
SELECT tag, COUNT(t1.exam_id) tag_cnt
FROM examination_info2 t1
JOIN exam_record2 t2 ON t1.exam_id = t2.exam_id
WHERE uid IN (
SELECT uid
FROM exam_record2
WHERE submit_time IS NOT NULL
AND DATE_FORMAT(submit_time,'%Y%m')='202309'
GROUP BY uid
HAVING COUNT(uid) >= 3
)
GROUP BY t1.exam_id
ORDER BY tag_cnt DESC;5.请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，按人数降序，相同人数的按平均分升序
用户信息表user_info：用户ID（uid），昵称(nick_name)，成就值(achievement)，等级(level)，职业方向(job)，注册时间(register_time)
试卷信息表examination_info:试卷ID(exam_id)，试卷类别（tag），试卷难度（difficulty），考试时长（duration），发布时间（release_time）
试卷作答记录表exam_record：用户ID（uid），试卷ID（exam_id），开始作答时间（start_time），交卷时（submit_time），得分（score）
drop table if exists examination_info3,user_info3,exam_record3;
CREATE TABLE examination_info3 (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE user_info3 (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int UNIQUE NOT NULL COMMENT '用户ID',
`nick_name` varchar(64) COMMENT '昵称',
achievement int COMMENT '成就值',
level int COMMENT '用户等级',
job varchar(32) COMMENT '职业方向',
register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE exam_record3 (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO user_info3(uid,`nick_name`,achievement,level,job,register_time) VALUES
(1001, '小a', 3100, 7, '算法', '2023-01-01 10:00:00'),
(1002, '小b', 2100, 6, '算法', '2023-01-01 10:00:00'),
(1003, '小c', 1500, 5, '算法', '2023-01-01 10:00:00'),
(1004, '小d', 1100, 4, '算法', '2023-01-01 10:00:00'),
(1005, '小e', 1600, 6, 'C++', '2023-01-01 10:00:00'),
(1006, '小小', 3000, 6, 'C++', '2023-01-01 10:00:00');
INSERT INTO examination_info3(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2023-09-01 06:00:00'),
(9002, 'C++', 'easy', 60, '2023-02-01 10:00:00'),
(9003, '算法', 'medium', 80, '2023-08-02 10:00:00');
INSERT INTO exam_record3(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2023-09-01 09:01:01', '2023-09-01 09:41:01', 70),
(1002, 9003, '2023-09-01 12:01:01', '2023-09-01 12:21:01', 60),
(1002, 9002, '2023-09-02 12:01:01', '2023-09-02 12:31:01', 70),
(1002, 9001, '2023-09-01 19:01:01', '2023-09-01 19:40:01', 80),
(1002, 9003, '2023-08-01 12:01:01', '2023-08-01 12:21:01', 60),
(1002, 9002, '2023-08-02 12:01:01', '2023-08-02 12:31:01', 70),
(1002, 9001, '2023-09-01 19:01:01', '2023-09-01 19:40:01', 85),
(1002, 9002, '2023-07-06 12:01:01', null, null),
(1003, 9003, '2023-09-07 10:01:01', '2023-09-07 10:31:01', 86),
(1003, 9003, '2023-09-08 12:01:01', '2023-09-08 12:11:01', 40),
(1003, 9001, '2023-09-01 13:01:01', '2023-09-01 13:41:01', 70),
(1003, 9002, '2023-09-08 14:01:01', null, null),
(1003, 9003, '2023-09-08 15:01:01', null, null),
(1005, 9001, '2023-09-01 12:01:01', '2023-09-01 12:31:01', 90),
(1005, 9002, '2023-09-01 12:01:01', '2023-09-01 12:31:01', 88),
(1005, 9002, '2023-09-02 12:11:01', '2023-09-02 12:31:01', 89);
#需求：请计算每张SQL类别试卷发布后，当天5级以上的用户作答的人数uv和平均分avg_score，
# 按人数降序，相同人数的按平均分升序
SELECT
t1.exam_id,
COUNT(DISTINCT t1.uid) uv,
ROUND(AVG(t1.score),1) avg_score
FROM exam_record3 t1
JOIN (SELECT exam_id, DATE(release_time) rt
FROM examination_info3
WHERE tag = 'SQL') t2
ON t1.exam_id = t2.exam_id
JOIN (SELECT uid
FROM user_info3
WHERE `level` > 5) t3
ON t1.uid = t3.uid
WHERE
t1.submit_time IS NOT NULL
AND DATE(t1.submit_time) = rt
GROUP BY t1.exam_id
ORDER BY uv DESC, avg_score ASC;6.请找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬，统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。结果按试卷完成数升序，按题目练习数降序
用户信息表user_info：用户ID（uid），昵称（nick_name），成就值（achievement），等级（level），职业方向（job），注册时间（register_time）
试卷信息表examination_info：试卷ID（exam_id），试卷类别（tag），试卷难度（difficulty），考试时长（duration），发布时间（release_time）
试卷作答记录表exam_record：用户ID（uid），试卷ID（exam_id），开始作答时（start_time），交卷时间（submit_time），得分（score）
题目练习记录表practice_record：用户ID（uid），题目ID（question_id），提交时间（submit_time），得分（score）
drop table if exists examination_info4,user_info4,exam_record4,practice_record4;
CREATE TABLE examination_info4 (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
exam_id int UNIQUE NOT NULL COMMENT '试卷ID',
tag varchar(32) COMMENT '类别标签',
difficulty varchar(8) COMMENT '难度',
duration int NOT NULL COMMENT '时长',
release_time datetime COMMENT '发布时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE user_info4 (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int UNIQUE NOT NULL COMMENT '用户ID',
`nick_name` varchar(64) COMMENT '昵称',
achievement int COMMENT '成就值',
level int COMMENT '用户等级',
job varchar(32) COMMENT '职业方向',
register_time datetime COMMENT '注册时间'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE practice_record4 (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
question_id int NOT NULL COMMENT '题目ID',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE exam_record4 (
id int PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid int NOT NULL COMMENT '用户ID',
exam_id int NOT NULL COMMENT '试卷ID',
start_time datetime NOT NULL COMMENT '开始时间',
submit_time datetime COMMENT '提交时间',
score tinyint COMMENT '得分'
)CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO user_info4(uid,`nick_name`,achievement,level,job,register_time) VALUES
(1001, '牛客1号', 3100, 7, '算法', '2020-01-01 10:00:00'),
(1002, '牛客2号', 2300, 7, '算法', '2020-01-01 10:00:00'),
(1003, '牛客3号', 2500, 7, '算法', '2020-01-01 10:00:00'),
(1004, '牛客4号', 1200, 5, '算法', '2020-01-01 10:00:00'),
(1005, '牛客5号', 1600, 6, 'C++', '2020-01-01 10:00:00'),
(1006, '牛客6号', 2000, 6, 'C++', '2020-01-01 10:00:00');
INSERT INTO examination_info4(exam_id,tag,difficulty,duration,release_time) VALUES
(9001, 'SQL', 'hard', 60, '2021-09-01 06:00:00'),
(9002, 'C++', 'hard', 60, '2021-09-01 06:00:00'),
(9003, '算法', 'medium', 80, '2021-09-01 10:00:00');
INSERT INTO practice_record4(uid,question_id,submit_time,score) VALUES
(1001, 8001, '2021-08-02 11:41:01', 60),
(1002, 8001, '2021-09-02 19:30:01', 50),
(1002, 8001, '2021-09-02 19:20:01', 70),
(1002, 8002, '2021-09-02 19:38:01', 70),
(1004, 8001, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1001, 8002, '2021-08-02 19:38:01', 70),
(1004, 8002, '2021-08-02 19:48:01', 90),
(1004, 8002, '2021-08-02 19:58:01', 94),
(1004, 8003, '2021-08-02 19:38:01', 70),
(1004, 8003, '2021-08-02 19:48:01', 90),
(1004, 8003, '2021-08-01 19:38:01', 80);
INSERT INTO exam_record4(uid,exam_id,start_time,submit_time,score) VALUES
(1001, 9001, '2021-09-01 09:01:01', '2021-09-01 09:31:00', 81),
(1002, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:01', 81),
(1003, 9001, '2021-09-01 19:01:01', '2021-09-01 19:40:01', 86),
(1003, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:51', 89),
(1004, 9001, '2021-09-01 19:01:01', '2021-09-01 19:30:01', 85),
(1005, 9002, '2021-09-01 12:01:01', '2021-09-01 12:31:02', 85),
(1006, 9003, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 84),
(1006, 9001, '2021-09-07 10:01:01', '2021-09-07 10:21:01', 80);
#请找到高难度SQL试卷得分平均值大于80并且是7级的红名大佬， 统计他们的2021年试卷总完成次数和题目总练习次数，只保留2021年有试卷完成记录的用户。 结果按试卷完成数升序，按题目练习数降序
SELECT t1.uid, exam_cnt, IFNULL(question_cnt,0) question_cnt
FROM(
SELECT t1.uid
FROM exam_record4 t1
JOIN user_info4 t2 ON t1.uid = t2.uid
JOIN examination_info4 t3 ON t1.exam_id = t3.exam_id
WHERE t3.tag = 'SQL' AND t3.difficulty = 'hard' AND t2.level = 7
GROUP BY t1.uid
HAVING AVG(t1.score) > 80
) t1
LEFT JOIN (
SELECT uid, COUNT(exam_id) exam_cnt
FROM exam_record4
WHERE YEAR(submit_time) = 2021
GROUP BY uid
) t2 ON t1.uid = t2.uid
LEFT JOIN (
SELECT uid,COUNT(question_id) question_cnt
FROM practice_record4
WHERE YEAR(submit_time) = 2021
GROUP BY uid
) t3 ON t1.uid = t3.uid
ORDER BY exam_cnt ASC,question_cnt DESC;
952
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
