牛客-SQL进阶挑战 里的题目，这里只记录1~25题
除了之前的查询，还包括创建表，创建索引等
-- 1. 插入数据
INSERT INTO exam_record (uid, exam_id, start_time, submit_time, score)
values (1001, 9001, "2021-09-01 22:11:12", "2021-09-01 23:01:12", 90),
(1002, 9002, "2021-09-04 07:01:02", null, null)
-- 省略列名，默认使用所有的列
-- 自增Id 填充null/default/0
INSERT INTO exam_record
values (null, 1001, 9001, "2021-09-01 22:11:12", "2021-09-01 23:01:12", 90),
(null, 1002, 9002, "2021-09-04 07:01:02", null, null)
-- 2. 插入，从另一张表中导入数据
-- 自增主键不要复制
INSERT INTO exam_record_before_2021(uid, exam_id, start_time, submit_time, score)
SELECT uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE submit_time like '2020%'
-- 填充0或null，不能用default
INSERT INTO exam_record_before_2021
SELECT null, uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE submit_time like '2020%'
-- 3. repalce into
-- 插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。
REPLACE INTO examination_info
values (null, 9003, "SQL", "hard", 90, "2021-01-01 00:00:00")
-- 7. delete + limit
DELETE FROM exam_record
WHERE submit_time is null or timestampdiff(minute, start_time, submit_time)<5
ORDER BY start_time
LIMIT 3
-- 8. 删除表
-- DROP TABLE 清除数据并且销毁表
-- TRUNCATE TABLE 只清除数据，保留表结构，列，权限，索引，视图，关
-- DELETE TABLE 删除（符合某些条件的）数据，执行后可以撤销
truncate exam_record
-- 9. 创建表
-- PRIMARY KEY -- 可选的约束，主键
-- FOREIGN KEY -- 外键，引用其他表的键值
-- AUTO_INCREMENT -- 自增ID
-- COMMENT comment -- 列注释（评论）
-- DEFAULT default_value -- 默认值
-- UNIQUE -- 唯一性约束，不允许两条记录该列值相同
-- NOT NULL -- 该列非空
CREATE TABLE IF NOT EXISTS user_info_vip(
id int(11) primary key auto_increment comment '自增ID',
uid int(11) unique not null comment '用户ID',
nick_name varchar(64) comment '昵称',
achievement int(11) default 0 comment '成就值',
level int(11) comment '用户等级',
job varchar(32) comment '职业方向',
register_time datetime default CURRENT_TIMESTAMP comment '注册时间'
)
-- 10. 修改表
ALTER TABLE user_info add school varchar(15) after level;
ALTER TABLE user_info change job profession varchar(10);
ALTER TABLE user_info modify achievement int(11) default 0;
-- 11. 删除多张表
drop table if EXISTS exam_record_2011, exam_record_2012, exam_record_2013, exam_record_2014;
-- 12. 创建索引
-- 普通索引、唯一索引、全文索引
CREATE INDEX idx_duration ON examination_info(duration);
CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id);
CREATE FULLTEXT INDEX full_idx_tag ON examination_info(tag);
-- 13. 删除索引
-- 好像不能用delete, 也不能一起删除
DROP INDEX uniq_idx_exam_id ON examination_info;
DROP INDEX full_idx_tag ON examination_info;
-- 14. 截断平均值
-- sum-min-max
SELECT tag, difficulty,
round((sum(score)-min(score)-max(score))/(count(score)-2), 1) as clip_avg_score
FROM examination_info
LEFT JOIN exam_record using(exam_id)
WHERE tag="SQL" and difficulty="hard"
GROUP BY tag
-- 15. distinct
-- + if
SELECT count(start_time) as total_pv,
count(submit_time) as complete_pv,
count(distinct if(score is null, null, exam_id)) as complete_exam_cnt
FROM exam_record
-- + case
SELECT count(start_time) as total_pv,
count(submit_time) as complete_pv,
count(distinct case when score is null then null else exam_id end) as complete_exam_cnt
FROM exam_record
-- 16. 得分不小于平均分的最小值
-- where 子句 >
SELECT min(score) as min_score_over_avg
FROM exam_record a
LEFT JOIN examination_info b using(exam_id)
WHERE b.tag = "SQL" and score>=(
SELECT avg(score) as avg_score
FROM exam_record a
LEFT JOIN examination_info b using(exam_id)
WHERE tag = "SQL"
)
-- 聚合窗口函数 avgSELECT min(score) as min_score_over_avg
FROM (
SELECT score, avg(score) over(partition by tag) as avg_score
FROM exam_record a
LEFT JOIN examination_info b using(exam_id)
WHERE tag = "SQL"
) t
WHERE score>=avg_score
SELECT month, count(score) as avg_active_days, count(distinct uid) as mau
FROM (
SELECT month(submit_time) as month, score, uid
FROM exam_record
) a
GROUP BY month
-- 17. 平均活跃天数和月活人数
-- 注意 同一个人一天活跃多次的情况, 只算一次, 因此要用distinct uid, day
-- 1）不行
SELECT month, round(count(distinct uid)/count(distinct uid), 2) as avg_active_days, count(distinct uid) as mau
# SELECT *
FROM (
SELECT date_format(submit_time, "%Y%m") as month, submit_time, score, uid
FROM exam_record
WHERE submit_time is not null and year(submit_time)=2021
) a
GROUP BY month
-- 2）行
SELECT month, round(count(distinct uid, day)/count(distinct uid), 2) as avg_active_days, count(distinct uid) as mau
# SELECT *
FROM (
SELECT date_format(submit_time, "%Y%m") as month, date_format(submit_time, "%Y%m%d") as day, submit_time, score, uid
FROM exam_record
WHERE submit_time is not null and year(submit_time)=2021
) a
GROUP BY month
-- 18. 计算月平均
-- max(day(last_day(submit_time))) 一个月的天数
SELECT date_format(submit_time, '%Y%m') as submit_month,
count(question_id) as month_q_cnt,
round(count(question_id) / max(day(last_day(submit_time))), 3) as avg_day_q_cnt
FROM practice_record
WHERE year(submit_time) = 2021
GROUP BY submit_month
UNION
SELECT concat(date_format(submit_time, '%Y'), "汇总") as submit_month,
count( question_id) as month_q_cnt,
round(count( question_id)/31, 3) as avg_day_q_cnt
FROM practice_record
WHERE year(submit_time) = 2021
GROUP BY submit_month
ORDER BY submit_month
-- 19. group_concat
-- group_concat([distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'])
-- 这里需要dictinct, 因为对于同一个uid，day+tag 可能有多个记录，例如tag="SQL"，day="2019-01-01", 但是有"Easy" 和 "Herd" 两个版本
SELECT uid,
count(start_time)-count(submit_time) as incomplete_cnt,
count(submit_time) as complete_cnt,
group_concat(distinct concat(date_format(start_time, "%Y-%m-%d"), ':', tag) separator ';') as detail
FROM exam_record as a
LEFT JOIN examination_info as b using(exam_id)
WHERE year(start_time)=2021
GROUP BY uid
HAVING complete_cnt>=1 and incomplete_cnt>1 and incomplete_cnt<5
ORDER BY incomplete_cnt desc
-- 20. 在月平均满足条件下，统计tag
-- 计算月平均 HAVING count(date_format(submit_time, "%Y%m")) / count(distinct date_format(submit_time, "%Y%m")) >=3
SELECT tag, count(*) as tag_cnt
FROM exam_record
LEFT JOIN examination_info using(exam_id)
WHERE uid in (
SELECT uid
FROM exam_record
GROUP BY uid
HAVING count(date_format(submit_time, "%Y%m")) / count(distinct date_format(submit_time, "%Y%m")) >=3
)
GROUP BY tag
ORDER BY tag_cnt desc
-- 21.考试记录+考试信息+用户信息
-- 三表联合查询
SELECT exam_id, count(distinct uid) as uv, round(avg(score), 1) as avg_score
FROM exam_record
LEFT JOIN user_info using(uid)
LEFT JOIN examination_info using(exam_id)
WHERE tag='SQL' and level>5
GROUP BY exam_id
ORDER BY uv desc, avg_score
-- 22. 和上一题类似
SELECT level, count(*) as level_cnt
FROM exam_record
LEFT JOIN user_info using(uid)
LEFT JOIN examination_info using(exam_id)
WHERE tag="SQL" and score>80
GROUP BY level
ORDER BY level_cnt desc
-- 23. union 要分别排序
-- union可以使用任何selcet语句，但order by子句只能在最后一次使用
-- 所以为了分开排序，就再套了一个select语句
SELECT * FROM (
SELECT exam_id as tid, count(distinct uid) as uv, count(*) as pv
FROM exam_record
GROUP BY exam_id
ORDER BY uv desc, pv desc
) a
UNION ALL
SELECT * FROM (
SELECT question_id as tid, count(distinct uid) as uv, count(*) as pv
FROM practice_record
GROUP BY question_id
ORDER BY uv desc, pv desc
) b
-- 24. 两种类别，用union
-- 注意顺序问题，where, group by, order by
SELECT uid, "activity1" as activity
FROM exam_record
LEFT JOIN examination_info using(exam_id)
WHERE year(start_time)=2021
GROUP BY uid
HAVING min(score)>=85
UNION
SELECT uid, "activity2" as activity
FROM exam_record
LEFT JOIN examination_info using(exam_id)
WHERE timestampdiff(second, start_time, submit_time) <= (duration*60)/2
and score>80
and difficulty="hard"
and year(start_time)=2021
ORDER BY uid
-- 25. 不亏为困难题
-- 关键，先查出符合条件的uid，再与practice_record联合
SELECT uid, exam_cnt, count(question_id) as question_cnt
FROM (
SELECT uid, count(score) as exam_cnt
FROM exam_record
WHERE uid in (
SELECT uid
FROM exam_record
LEFT JOIN examination_info using(exam_id)
LEFT JOIN user_info using(uid)
WHERE tag="SQL" and difficulty="hard" and level=7
GROUP BY uid
HAVING avg(score)>80
) and year(submit_time)=2021
GROUP BY uid
HAVING count(score)>=1
) a
LEFT JOIN practice_record b using(uid)
WHERE year(submit_time)=2021 or submit_time is null # 未做题不能去掉，应为0
GROUP BY uid
ORDER BY exam_cnt, question_cnt desc