https://javaguide.cn/database/sql/sql-questions-01.html
小总结
using()函数适用联表字段一致情况的join
having能使用select别名筛选
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY items
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
mysql having别名以及having和where的区别,group by也能使用别名!
https://blog.csdn.net/weixin_45820558/article/details/136840928
mysql select非group by非聚合函数查询
5.7.5之前
下面可执行
5.7.5
默认开启ONLY_FULL_GROUP_BY,除了聚合函数以及非group by的字段出现在select
默认是使用any_value()返回其中随机一个
笛卡尔积,left join,right join,full join之前的区别
DATE_FORMAT函数用法
https://blog.csdn.net/weixin_41400063/article/details/83150919
把字符串转为日期格式
SELECT DATE_FORMAT('2011-09-20 08:30:45','%Y-%m-%d %H:%i:%S');
把日期转为字符串格式
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S');
注意前缀必须是正常格式时间/时间字符串
错误示范
DATE获取日期
select DATE(now());
2024-03-20
SUBSTRING使用的第几位,而不是索引
SUBSTRING(cust_city, 1, 3)
使用 union 组合查询时，只能使用一条 order by 字句，他必须位于最后一条 select 语句之后
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'MI'
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'IL'
ORDER BY cust_name;
错误案例的 SQL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'MI'
ORDER BY cust_name;
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'IL'ORDER BY cust_name;
错误案例的 SQL
如果必须实现怎么办,两个查询sql的union?
包装为两个子查询的union,注意子查询需要设置别名t1,t2
DML
https://javaguide.cn/database/sql/sql-questions-02.html
时间比较
入参1 大 入参2 小
select HOUR(TIMEDIFF(now(),'2024-03-19 12:10:00'));
入参2 小 入参3 大
select TIMESTAMPDIFF(HOUR ,now(),'2024-03-19 12:10:00');
单条插入
insert into
insert into select xxx
REPLACE INTO
删除记录（二）
删除指定条件+排序+x条数据
DELETE FROM exam_record WHERE submit_time IS null OR TIMESTAMPDIFF(MINUTE, start_time, submit_time) < 5
ORDER BY start_time
LIMIT 3
https://javaguide.cn/database/sql/sql-questions-03.html
MySQL8的缓存模板表With as
WITH t1 AS
(SELECT record.*,
info.tag,
info.difficulty
FROM exam_record record
INNER JOIN examination_info info ON record.exam_id = info.exam_id
WHERE info.tag = "SQL"
AND info.difficulty = "hard" )
多个with as语句错误
with t1 as (
select uid,count(submit_time is null) uncommit,count(submit_time) commit from exam_record group by uid having uncommit<5 and commit>=1 and uncommit>1
)
with t2 as (
select uid,group_concat(DATE_FORMAT(submit_time,'%Y-%m-%d'),ei.tag) detail from exam_record
left join examination_info ei on exam_record.exam_id = ei.exam_id where uid=1002 group by uid
)
select * from t1 inner join t2 using (uid);
应该合并为一个with
WITH t1 AS (
SELECT
uid,
COUNT(submit_time IS NULL) AS uncommit,
COUNT(submit_time) AS commit
FROM
exam_record
GROUP BY
uid
HAVING
uncommit < 5 AND commit >= 1 AND uncommit > 1
),
t2 AS (
SELECT
uid,
GROUP_CONCAT(DATE_FORMAT(submit_time, '%Y-%m-%d'), ei.tag) AS detail
FROM
exam_record er
LEFT JOIN
examination_info ei ON er.exam_id = ei.exam_id
WHERE
uid = 1002
GROUP BY
uid
)
SELECT
*
FROM
t1
INNER JOIN
t2 USING (uid);
SELECT中可以嵌套子查询
COUNT( DISTINCT exam_id, score IS NOT NULL OR NULL ),count(distinct xxx),包含多列,其一列为NULL则不统计数量
SELECT
count(*) total_pv,
( SELECT count(*) FROM exam_record WHERE submit_time IS NOT NULL ) complete_pv,
( SELECT COUNT( DISTINCT exam_id, score IS NOT NULL OR NULL ) FROM exam_record ) complete_exam_cnt
FROM
exam_record
同理avg(score)/min(score)/max(score)/sum(score)也不会统计NULL值
select avg(score) from exam_record where score is not null;
注意count(0)也为1,所以在使用count(submit_time is null)应该改为
select count(0) from dual;
select uid,count(submit_time is null) uncommit,count(submit_time) commit from exam_record
获取查询月有多少日,最大DAYS
SELECT DAY(LAST_DAY('2023-07-08')) AS days_in_month;
-- 输出：31
mysql if语句
IF(condition, value_if_true, value_if_false)
if(submit_time,NULL,1),默认condition也是判断非空,所以submit_time!=null,返回NULL,submit_time==null,返回1
等同于if(submit_time is not null,NULL,1)
等同于submit_time is null or null
select uid,if(submit_time,NULL,1) uncommit from exam_record
select uid,submit_time is null or null uncommit from exam_record
CASE WHEN
SELECT uid,
CASE
WHEN submit_time IS NOT NULL THEN NULL
ELSE 1
END AS uncommit
FROM exam_record;
NULL值默认处理
COALESCE
COALESCE 是 MySQL 中的一个函数，用于从一组值中返回第一个非 NULL 的值。它的语法如下：
常常用于如果是NULL则设置默认值
COALESCE(value1, value2, ...)
NULLIF
NULLIF(expression1, expression2)
其中 expression1 和 expression2 是要比较的表达式。如果它们相等，则返回 NULL；否则返回 expression1 的值。
SELECT NULLIF(submit_time, NULL) AS uncommit FROM exam_record;
LEFT JOIN practice_record pr ON er.uid = pr.uid AND xxx
left join on xxx and 还能继续筛选条件,还能筛选双表!!!
是不是等同于where里面+条件?
SELECT er.uid AS UID,
count(DISTINCT er.exam_id) AS exam_cnt,
count(DISTINCT pr.id) AS question_cnt
FROM exam_record er
LEFT JOIN practice_record pr ON er.uid = pr.uid
AND YEAR (er.submit_time)= 2021
AND YEAR (pr.submit_time)= 2021
驱动表与被驱动表的概念
(https://www.jianshu.com/p/38a8b99798a1)
驱动表是表连接中的基础表，也就是通过驱动表的数据结果集作为循环基础数据，然后一条一条的通过这个结果集的数据作为过滤条件到被驱动表中查询数据，然后合并
驱动与被驱动
左连接中 左表是驱动表，右表是被驱动表
右连接中 右表是驱动表，左表是被驱动表
内连接中 表数据量较小的表会由mysql自动选择作为驱动表去驱动大表
有一个重点是，如果where条件存在的话 mysql会根据where实际条件进行驱动表的选择
sql优化中，一个比较重要的点就是要用小表驱动大表
原因
mysql表关联的算法，是通过驱动表去循环被驱动表，比如说，20w的大表和200条的小表，如果大表驱动，那么是20w条记录外循环，内循环200条去连接查找，需要通过20w次连接，如果小表驱动，那么是200条记录外循环，内循环20w条去连接查找，只需要通过200次连接就可以了，并且驱动表是不会使用索引的
CASE WHEN和IF条件使用=,以及多重嵌套
union联合tag标签区分两表分别统计
SELECT score,
CASE
WHEN score >= 90 THEN '优秀'
WHEN score >= 80 THEN '良好'
WHEN score >= 60 THEN '及格'
ELSE '不及格'
END AS grade
FROM student_scores;
SELECT
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN
CASE
WHEN sub_condition1 THEN sub_result1
WHEN sub_condition2 THEN sub_result2
ELSE sub_resultN
END
ELSE resultN
END AS final_result
FROM your_table;
if(tag = 'question' and year(submit_time) = 2021,date_format(submit_time,'%Y-%m-%d'),NULL))
# 请统计每个 6/7 级用户
# 总活跃月份数、
# 2021 年活跃天数、
# 2021 年试卷作答活跃天数、
# 2021 年答题活跃天数，
# 按照总活跃月份数降序排序、2021 年活跃天数降序排序。由示例数据结果输出如下：
with t1 as (
select uid
from user_info where level between 6 and 7
)
select uid,
count(distinct date_format(submit_time,'%Y-%m')) act_month,
count(distinct if(year(submit_time) = 2021,date_format(submit_time,'%Y-%m-%d'),NULL)) 2021_act_day,
count(distinct if(tag = 'exam',if(year(submit_time) = 2021,date_format(submit_time,'%Y-%m-%d'),NULL),NULL)) 2021_exam_act_day,
count(distinct if(tag = 'question' and year(submit_time) = 2021,date_format(submit_time,'%Y-%m-%d'),NULL)) 2021_que_act_day
from (
select id,uid,exam_id,submit_time,score,'exam' tag
from exam_record
union all
select *,'question' from practice_record
)t2 where uid in (select uid from t1) group by uid order by act_month desc ,2021_act_day desc ;
# 标准答案
SELECT user_info.uid,
count(DISTINCT act_month) AS act_month_total,
count(DISTINCT CASE
WHEN YEAR (act_time)= '2021' THEN act_day
END) AS act_days_2021,
count(DISTINCT CASE
WHEN YEAR (act_time)= '2021'
AND tag = 'exam' THEN act_day
END) AS act_days_2021_exam,
count(DISTINCT CASE
WHEN YEAR (act_time)= '2021'
AND tag = 'question' THEN act_day
END) AS act_days_2021_question
FROM
(SELECT UID,
exam_id AS ans_id,
start_time AS act_time,
date_format(start_time, '%Y%m') AS act_month,
date_format(start_time, '%Y%m%d') AS act_day,
'exam' AS tag
FROM exam_record
UNION ALL SELECT UID,
question_id AS ans_id,
submit_time AS act_time,
date_format(submit_time, '%Y%m') AS act_month,
date_format(submit_time, '%Y%m%d') AS act_day,
'question' AS tag
FROM practice_record) total
RIGHT JOIN user_info ON total.uid = user_info.uid
WHERE user_info.LEVEL IN (6,7)
GROUP BY user_info.uid
ORDER BY act_month_total DESC,
act_days_2021 DESC
https://github.com/Snailclimb/JavaGuide/blob/main/docs/database/sql/sql-questions-04.md
开窗函数
rank和row_number区别
rank分数相等排名相等,1,2,2,4
group by配合开窗函数一定要包含partition by tag,才能继续order by聚合函数+group by分组值,
# 每类试卷得分前三名
select uid,tag,rank() over (partition by tag order by max(score) desc,min(score) desc ) rk
from exam_record left join examination_info ei on exam_record.exam_id = ei.exam_id
group by uid,tag
SELECT tag,
UID,
ranking
FROM
(SELECT b.tag AS tag,
a.uid AS UID,
ROW_NUMBER() OVER (PARTITION BY b.tag
ORDER BY b.tag,
max(a.score) DESC,
min(a.score) DESC,
a.uid DESC) AS ranking
FROM exam_record a
LEFT JOIN examination_info b ON a.exam_id = b.exam_id
GROUP BY b.tag,
a.uid) t
WHERE ranking <= 3
having配合max(if指定筛选)来判断是否保留行
group by融合组
HAVING (max(IF (rn1 = 2, a.timex, 0))- max(IF (rn2 = 2, a.timex, 0)))/ 60 > b.duration / 2
SELECT a.exam_id,
b.duration,
b.release_time
FROM
(SELECT exam_id,
row_number() OVER (PARTITION BY exam_id
ORDER BY timestampdiff(SECOND, start_time, submit_time) DESC) rn1,
row_number() OVER (PARTITION BY exam_id
ORDER BY timestampdiff(SECOND, start_time, submit_time) ASC) rn2,
timestampdiff(SECOND, start_time, submit_time) timex
FROM exam_record
WHERE score IS NOT NULL ) a
INNER JOIN examination_info b ON a.exam_id = b.exam_id
GROUP BY a.exam_id,
b.duration,
b.release_time
HAVING (max(IF (rn1 = 2, a.timex, 0))- max(IF (rn2 = 2, a.timex, 0)))/ 60 > b.duration / 2
ORDER BY a.exam_id DESC
连续两次作答试卷的最大时间窗（较难）
前提2021年,连续两次作答试卷的最大时间窗days_window
然后获取最开始时间,最后一次时间跨度时间内,完成卷次比值 * (两次最大exam时间窗口)
1.group by uid,max_between,其中这个时间区间是相同值,必须group by才能输出
可以优化first_value,last_value,已经group by了可以直接使用max(start_time)-min(start_time)即可
2.max(timestampdiff(day, start_time,if(next is null,start_time,next))),如果next为null,比如向下查询的 lead(start_time,1,NULL)
找到了最后,那么默认值返回next=Null,如果移除,那么在count(start_time)缺少
select *,round(ct/(max_between)*max_next,2) from
(
select uid,
max(datediff(if(next is null,start_time,next),start_time))+1 max_next,
datediff(last,first)+1 max_between,
count(start_time) ct
from
(select *,
lead(start_time,1,NULL) over (partition by uid order by start_time asc) next,
first_value(start_time) over (partition by uid) first,
last_value(start_time) over (partition by uid) last
from exam_record where year(start_time)=2021 )t group by uid,max_between
having ct>1
) t;
SELECT UID,
max(datediff(next_time, start_time)) + 1 AS days_window,
round(count(start_time)/(datediff(max(start_time), min(start_time))+ 1) * (max(datediff(next_time, start_time))+ 1), 2) AS avg_exam_cnt
FROM
(SELECT UID,
start_time,
lead(start_time, 1) OVER (PARTITION BY UID
ORDER BY start_time) AS next_time
FROM exam_record
WHERE YEAR (start_time) = '2021' ) a
GROUP BY UID
HAVING count(DISTINCT date(start_time)) > 1
ORDER BY days_window DESC,
avg_exam_cnt DESC
rank()和dense_rank()区别
rank->1,1,3,相同排名占用名额
dense_rank->1,1,2,相同排名不占用名额
找到每个人近三个有试卷作答记录的月份中没有试卷是未完成状态的用户的试卷作答完成数
答案巧妙,没有提交卷肯定没有score,那么count(dr)必定等于行数但是count(score)不统计NULL值行
我的min(if(submit_time is null,0,submit_time)) != 0;是否保留分组看,是否存在组内有submit_time is null的数据
select uid,count(submit_time)
from
(
select *,
dense_rank() over (partition by uid order by date_format(start_time,'%Y-%m') desc) drk
from exam_record ) t1
where drk<=3 group by uid having min(if(submit_time is null,0,submit_time)) != 0;
SELECT UID,
count(score) exam_complete_cnt
FROM
(SELECT *, DENSE_RANK() OVER (PARTITION BY UID ORDER BY date_format(start_time, '%Y%m') DESC) dr
FROM exam_record) t1
WHERE dr <= 3
GROUP BY UID
HAVING count(dr)= count(score)
ORDER BY exam_complete_cnt DESC,UID DESC
请统计 SQL 试卷上未完成率较高的 50%用户中，6 级和 7 级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户 ID、月份升序排序。
未完成的统计次数怎么count?=>sumbit_time=null的count
方法一 :
count(submit_time is null or null) udo,已知聚合函数不统计NULL,那么submit_time is null?1:null;
如果有值,那么执行or 语句返回null
方法二:
COUNT(IF(submit_time IS NULL, 1, NULL))
with t1 as (
select uid
from (
select *,percent_rank() over (order by per) prk
from (
select *,udo/`all` per
from (
select uid,count(start_time) `all`,count(submit_time is null or null) udo
from exam_record where exam_id in (9001,9002) group by uid
) t1
)t2
)t3
left join user_info using (uid)
where prk>=0.5 and level in (6,7)
)
select uid,date_format(start_time,'%Y%m') month,count(start_time) total_cnt,count(submit_time) complete_cnt
from (
SELECT *,
DENSE_RANK() OVER (PARTITION BY UID ORDER BY date_format(start_time,'%Y%m') DESC) dr
FROM exam_record where uid in (select * from t1)
)t2 group by uid,date_format(start_time,'%Y%m');
-- 答案
SELECT t1.uid,
t1.month_d,
count(*) AS total_cnt,
count(t1.submit_time) AS complete_cnt
FROM-- 先求出未完成率的排名
(SELECT UID,
count(submit_time IS NULL OR NULL)/ count(start_time) AS num,
PERCENT_RANK() OVER (
ORDER BY count(submit_time IS NULL OR NULL)/ count(start_time)) AS ranking
FROM exam_record
LEFT JOIN examination_info USING (exam_id)
WHERE tag = 'SQL'
GROUP BY UID) t
INNER JOIN
(-- 再求出近三个月的练习记录
SELECT UID,
date_format(start_time, '%Y%m') AS month_d,
submit_time,
exam_id,
dense_rank() OVER (PARTITION BY UID
ORDER BY date_format(start_time, '%Y%m') DESC) AS ranking
FROM exam_record
LEFT JOIN user_info USING (UID)
WHERE LEVEL IN (6,7) ) t1 USING (UID)
WHERE t1.ranking <= 3 AND t.ranking >= 0.5 -- 使用限制找到符合条件的记录
GROUP BY t1.uid,
t1.month_d
ORDER BY t1.uid,
t1.month_d
请计算 2021 年上半年各类试卷的做完次数相比 2020 年上半年同期的增长率（百分比格式，保留 1 位小数），以及做完次数排名变化，按增长率和 21 年排名降序输出。
1.关于取小数点后几位%问题解决
2.BIGINT UNSIGNED value is out of range in …
BIGINT UNSIGNED: 无符号大整型数，也就是只能是正整数。
如果两个数相减就有可能出现负数，因此会报错超出下限范围，
所以需要转换数据类型：CAST(jc.user_level AS signed)
# 排行并列模式
select *,CAST(complete_rk_21 AS SIGNED)-CAST(complete_rk_20 AS SIGNED) rank_delta
from (
select *,
# 先乘以再取小数位,不然会始终有保留小数点后几位=>余小数位数-33.300%
CONCAT(round((complete_21-complete_20)/complete_20,3)*100,'%'),
# 返回-33.3%
CONCAT(round((complete_21-complete_20)/complete_20*100,1),'%'),
rank() over (order by complete_20 desc ) complete_rk_20,
rank() over (order by complete_21 desc ) complete_rk_21
# 以及做完次数排名变化
from (
select
exam_id,
count(year(submit_time)=2021 and month(submit_time)<=6 or null) complete_21,
count(year(submit_time)=2020 and month(submit_time)<=6 or null) complete_20
from exam_record group by exam_id
) t
)t2;
每份试卷每月作答数和截止当月的作答总数
sum也可以使用开窗函数
rows between [arg1] and [arg2]
arg1可选unbounded preceding 1 preceding current row 1 follow
arg2可选unbounded follow 1 preceding current row 1 follow
over包含order by默认值开窗,rows between unbounded preceding and current row,之前值到当前值
over不包含order by默认行开窗,所有行
sum(ct) over (partition by exam_id order by ym rows between unbounded preceding and current row) end
= sum(ct) over (partition by exam_id order by ym )
请输出自从有用户作答记录以来
每月的试卷作答记录中月活用户数、
新增用户数、如何判断是新增用户,如果当月是用户min(start_time)则为新增
截止当月的单月最大新增用户数、max(uv_add) over(order by first_start)
截止当月的累积用户数。累计直接sum(uv_add) over(order by first_start)
结果按月份升序输出。
-- 我的
select *,max(add_uv) over (order by first_start),sum(add_uv) over (order by first_start)
from (
select date_format(start_time, '%Y%m') first_start,count(distinct uid) uv
from exam_record
group by first_start
)t
left join
(
select first_start,count(uid) add_uv from (
select uid,min(date_format(start_time, '%Y%m')) first_start
from exam_record
group by uid
) t1 group by first_start
) t2 using(first_start);
-- 截止当月的单月最大新增用户数、截止当月的累积用户数，按月份升序输出
SELECT
start_month,
mau,
month_add_uv,
max( month_add_uv ) over ( ORDER BY start_month ),
sum( month_add_uv ) over ( ORDER BY start_month )
FROM
(
-- 统计每月的月活和新增用户数
SELECT
date_format( a.start_time, '%Y%m' ) AS start_month,
count( DISTINCT a.uid ) AS mau,
count( DISTINCT b.uid ) AS month_add_uv
FROM
exam_record a
LEFT JOIN (
-- 统计每个人的首次登陆月份
SELECT uid, min( date_format( start_time, '%Y%m' )) AS first_month FROM exam_record GROUP BY uid ) b ON date_format( a.start_time, '%Y%m' ) = b.first_month
GROUP BY
start_month
) main
ORDER BY
start_month
https://github.com/Snailclimb/JavaGuide/blob/main/docs/database/sql/sql-questions-05.md
请统计有未完成状态的试卷的未完成数 incomplete_cnt 和未完成率 incomplete_rate
空值处理
select sum(1 IS NULL) from dual; 1!=null返回0,不满足条件,如果为Null返回1计数
count(submit_time is null or null)=>submit_time is null submit_time =null返回1 ,否则返回Null,聚合函数不统计null
count(submit_time is null),如果不满足则count(0)也会计入,所以count(1)/count(*)是统计行数,
select exam_id,
count(submit_time is null or null) incompelete,
count(submit_time is null or null)/count(1) incompelete_per
from exam_record group by exam_id;
-- -------------------------------------------------------------------------
SELECT exam_id,
count(submit_time IS NULL OR NULL) incomplete_cnt,
ROUND(count(submit_time IS NULL OR NULL) / count(*), 3) complete_rate
FROM exam_record
GROUP BY exam_id
HAVING incomplete_cnt <> 0;
-- -------------------------------------------------------------------------
SELECT exam_id,
sum(submit_time IS NULL) incomplete_cnt,
ROUND(sum(submit_time IS NULL) / count(*), 3) complete_rate
FROM exam_record
GROUP BY exam_id
HAVING incomplete_cnt <> 0;
-- -------------------------------------------------------------------------
select sum(1 IS NULL) from dual;
请输出每个 0 级用户所有的高难度试卷考试平均用时和平均得分，未完成的默认试卷最大考试时长和 0 分处理。由示例数据结果输出如下：
right join,不用right join会输出exam_record未匹配的exam_id的数据
答案对于
null值预先处理,花费时间当submit_time IS NOT NULL,计算花费时间,否则就是duration
得分当submit_time IS NOT NULL,赋值score,否则是0
select uid,sum(score)/count(1),sum(nullif(timestampdiff(minute,start_time,submit_time),duration))/count(1)
from exam_record
right join
(
select exam_id,duration
from examination_info where difficulty='hard'
) t using(exam_id)
where uid in
(
select uid
from user_info where level=0
)
group by uid;
-- ----------------------------------------------------------------------------------------------
SELECT UID,
round(avg(new_socre)) AS avg_score,
round(avg(time_diff), 1) AS avg_time_took
FROM
(SELECT er.uid,
IF (er.submit_time IS NOT NULL, TIMESTAMPDIFF(MINUTE, start_time, submit_time), ef.duration) AS time_diff,
IF (er.submit_time IS NOT NULL,er.score,0) AS new_socre
FROM exam_record er
LEFT JOIN user_info uf ON er.uid = uf.uid
LEFT JOIN examination_info ef ON er.exam_id = ef.exam_id
WHERE uf.LEVEL = 0 AND ef.difficulty = 'hard' ) t
GROUP BY UID
ORDER BY UID
请找到昵称以『牛客』开头『号』结尾、成就值在 1200~2500 之间，且最近一次活跃（答题或作答试卷）在 2021 年 9 月的用户信息。
题目有问题:最近一次活跃（答题或作答试卷）在 2021 年 9 月答案表明是存在2021年9月答题的记录
三联表,以user_info为主表的left join,猜想联表的结果是怎么样子?
-- 返回 a*b*c
SELECT *
FROM user_info u_info
LEFT JOIN exam_record record ON record.uid = u_info.uid
LEFT JOIN practice_record pr ON u_info.uid = pr.uid
where pr.uid=1002;
-- 考试1002表有18条
select * from exam_record where uid=1002;
-- 题目1002表有3条
select * from practice_record where uid=1002;
-- 结果是1*18*3=54条 用户为1条数据
SELECT DISTINCT u_info.uid,
u_info.nick_name,
u_info.achievement
FROM user_info u_info
LEFT JOIN exam_record record ON record.uid = u_info.uid
LEFT JOIN practice_record pr ON u_info.uid = pr.uid
WHERE u_info.nick_name LIKE "牛客%号"
AND u_info.achievement BETWEEN 1200
AND 2500
AND (date_format(record.submit_time, '%Y%m')= 202109
OR date_format(pr.submit_time, '%Y%m')= 202109)
GROUP BY u_info.uid
找到昵称以"牛客"+纯数字+"号"或者纯数字组成的用户对于字母 c 开头的试卷类别（如 C,C++,c#等）的已完成的试卷 ID 和平均得分，按用户 ID、平均分升序排序。由示例数据结果输出如下：
RLIKE 正则匹配
tag RLIKE "[1]" 开头匹配c或者C
SELECT UID,
exam_id,
ROUND(AVG(score), 0) avg_score
FROM exam_record
WHERE UID IN
(SELECT UID
FROM user_info
WHERE nick_name RLIKE "^牛客[0-9]+号 $"
OR nick_name RLIKE "^[0-9]+$")
AND exam_id IN
(SELECT exam_id
FROM examination_info
WHERE tag RLIKE "^[cC]")
AND score IS NOT NULL
GROUP BY UID,exam_id
ORDER BY UID,avg_score;
请你筛选表中的数据，
当有任意一个 0 级用户未完成试卷数大于 2 时，输出每个 0 级用户的试卷未完成数和未完成率（保留 3 位小数）；
若不存在这样的用户，则输出所有有作答记录的用户的这两个指标。结果按未完成率升序排序。
控制条件输出数据分析如何做
v1版本是首先查询所有条件结果, 0 级用户未完成试卷数大于 2==>host_user
,所得所有0级用户的试卷未完成数和未完成率==>tt1
否则输出所有用户的试卷未完成数和未完成率==>tt2
怎么控制输出?输出哪一个列是根据0 级用户未完成试卷数大于 2==>host_user,是否存在用户来判断
LEFT JOIN
(SELECT UID
FROM host_user) t1 ON 1 = 1,无条件链接,笛卡尔积,等同from t1,t2
where取判断WHERE t1.uid IS NOT NULL
再通过UNION ALL来上下连接
第二个结束判断
where取判断WHERE t1.uid IS NULL
WITH host_user AS
(SELECT ui.uid UID
FROM user_info ui
LEFT JOIN exam_record er ON ui.uid = er.uid
WHERE ui.uid IN
(SELECT ui.uid
FROM user_info ui
LEFT JOIN exam_record er ON ui.uid = er.uid
WHERE er.submit_time IS NULL
AND ui.LEVEL = 0 )
GROUP BY ui.uid
HAVING sum(IF (er.submit_time IS NULL, 1, 0))> 2),
tt1 AS
(SELECT tmp1.uid UID,
sum(IF (er.submit_time IS NULL
AND er.start_time IS NOT NULL, 1, 0)) incomplete_cnt,
round(sum(IF (er.submit_time IS NULL
AND er.start_time IS NOT NULL, 1, 0))/ count(tmp1.uid), 3) incomplete_rate
FROM
(SELECT DISTINCT ui.uid
FROM user_info ui
LEFT JOIN exam_record er ON ui.uid = er.uid
WHERE er.submit_time IS NULL
AND ui.LEVEL = 0 ) tmp1
LEFT JOIN exam_record er ON tmp1.uid = er.uid
GROUP BY tmp1.uid
ORDER BY incomplete_rate),
tt2 AS
(SELECT ui.uid UID,
sum(CASE
WHEN er.submit_time IS NULL
AND er.start_time IS NOT NULL THEN 1
ELSE 0
END) incomplete_cnt,
round(sum(IF (er.submit_time IS NULL
AND er.start_time IS NOT NULL, 1, 0))/ count(ui.uid), 3) incomplete_rate
FROM user_info ui
JOIN exam_record er ON ui.uid = er.uid
GROUP BY ui.uid
ORDER BY incomplete_rate)
(SELECT tt1.*
FROM tt1
LEFT JOIN
(SELECT UID
FROM host_user) t1 ON 1 = 1
WHERE t1.uid IS NOT NULL )
UNION ALL
(SELECT tt2.*
FROM tt2
LEFT JOIN
(SELECT UID
FROM host_user) t2 ON 1 = 1
WHERE t2.uid IS NULL)
v2版本
想想控制条件,如果存在就输出仅仅为level=0,否则输出用户不筛选
where里面嵌套case when 语句来筛选走那一部分userid,case when拼接sql
注意写法,case when (查询条件结果)>2 then 拼接where的sql end
SELECT
ui.uid,
SUM(
IF
( start_time IS NOT NULL AND score IS NULL, 1, 0 )) AS incomplete_cnt,#3.试卷未完成数
ROUND( AVG( IF ( start_time IS NOT NULL AND score IS NULL, 1, 0 )), 3 ) AS incomplete_rate #4.未完成率
FROM
user_info ui
LEFT JOIN exam_record USING ( uid )
WHERE
CASE
WHEN (#1.当有任意一个0级用户未完成试卷数大于2时
SELECT
MAX( lv0_incom_cnt )
FROM
(
SELECT
SUM(
IF
( score IS NULL, 1, 0 )) AS lv0_incom_cnt
FROM
user_info
JOIN exam_record USING ( uid )
WHERE
LEVEL = 0
GROUP BY
uid
) table1
)> 2 THEN
uid IN ( #1.1找出每个0级用户
SELECT uid FROM user_info WHERE LEVEL = 0 ) ELSE uid IN ( #2.若不存在这样的用户，找出有作答记录的用户
SELECT DISTINCT uid FROM exam_record )
END
GROUP BY
ui.uid
ORDER BY
incomplete_rate #5.结果按未完成率升序排序
# 为了得到用户试卷作答的定性表现，
# 我们将试卷得分按分界点[90,75,60]分为优良中差四个得分等级（分界点划分到左区间），
# 请统计不同用户等级的人在完成过的试卷中各得分等级占比（结果保留 3 位小数），
# 未完成过试卷的用户无需输出，
# 结果按用户等级降序、占比降序排序。
select *,score_grade_ct/level_ct rate from (
select *,sum(score_grade_ct) over(partition by level) level_ct from (
select level,case when score>=90 then '优'
when score>=75 then '良'
when score>=60 then '中'
else '差'
end score_grade,
count(1) score_grade_ct
from exam_record
inner join user_info ui on exam_record.uid = ui.uid
where submit_time is not null group by level,score_grade
)t
)t2 order by level desc ,rate desc
-- 请从中找到注册时间最早的 3 个人。由示例数据结果输出如下：
不要复杂化了,delete可以指定删除限制条数
由小到大,由大到小包含1-3,直接limit
select *
from user_info order by register_time asc limit 3;
找到求职方向为算法工程师，且注册当天就完成了算法类试卷的人，
按参加过的所有考试最高得分排名。排名榜很长，我们将采用
分页展示，每页 3 条，现在需要你取出第 3 页（页码从 1 开始）的人的信息。
学习既然要到了ymd为什么不用date=>date(t.submit_time) = date(t1.register_time)
limit忘记怎么用
SELECT * FROM users LIMIT 10; -- 返回前 10 行
SELECT * FROM users LIMIT 5, 10; -- 从第 6 行开始，返回 10 行
select *
from exam_record
left join examination_info ei on exam_record.exam_id = ei.exam_id
left join test.user_info ui on exam_record.uid = ui.uid
where job='算法' and tag='算法' and date_format(register_time,'%Y%m%d')=date_format(submit_time,'%Y%m%d')
order by score limit 6,3;
SELECT t1.uid,
LEVEL,
register_time,
max(score) AS max_score
FROM exam_record t
JOIN examination_info USING (exam_id)
JOIN user_info t1 ON t.uid = t1.uid
AND date(t.submit_time) = date(t1.register_time)
WHERE job = '算法'
AND tag = '算法'
GROUP BY t1.uid,
LEVEL,
register_time
ORDER BY max_score DESC
limit 6,3;
修复串列了的记录
substring_index最后参数是保留几个item,负数就从后获取
SELECT
exam_id,
substring_index( tag, ',', 1 ) tag,
substring_index( substring_index( tag, ',', 2 ), ',',- 1 ) difficulty,
substring_index( tag, ',',- 1 ) duration
FROM
examination_info
WHERE
difficulty = ''
对过长的昵称截取处理
LENGTH和CHAR_LENGTH区别
LENGTH函数：它返回给定字符串的字节数。对于包含多字节字符的字符串，每个字符都会被当作一个字节来计算。
示例：
SELECT LENGTH('你好'); -- 输出结果：6，因为 '你好' 中的每个汉字每个占3个字节
CHAR_LENGTH函数：它返回给定字符串的字符数。对于包含多字节字符的字符串，每个字符会被当作一个字符来计算。
示例：
SELECT CHAR_LENGTH('你好'); -- 输出结果：2，因为 '你好' 中有两个字符，即两个汉字
select if(char_length(nick_name)>12,concat(substring(nick_name,1,10),'...'),nick_name)
from user_info
-- ------------------------------------------------------
SELECT
uid,
CASE
WHEN CHAR_LENGTH( nick_name ) > 13 THEN
CONCAT( SUBSTR( nick_name, 1, 10 ), '...' ) ELSE nick_name
END AS nick_name
FROM
user_info
WHERE
CHAR_LENGTH( nick_name ) > 10
GROUP BY
uid;
大小写混乱时的筛选统计（较难）
试卷的类别 tag 可能出现大小写混乱的情况，
请先筛选出试卷作答数小于 3 的类别 tag，统计将其转换为大写后对应的原本试卷作答数。
如果转换后 tag 并没有发生变化，不输出该条结果。
题目有问题,根据答案猜题目
WITH a AS
(SELECT tag,
COUNT(start_time) AS answer_cnt
FROM exam_record er
JOIN examination_info ei ON er.exam_id = ei.exam_id
GROUP BY tag)
SELECT a.tag,
b.answer_cnt
FROM a
INNER JOIN a AS b ON UPPER(a.tag)= b.tag #a小写 b大写
AND a.tag != b.tag
WHERE a.answer_cnt < 3;
临时表a
SELECT tag,
COUNT(start_time) AS answer_cnt
FROM exam_record er
JOIN examination_info ei ON er.exam_id = ei.exam_id
GROUP BY tag
自连接,如果INNER JOIN a AS b ON UPPER(a.tag)= b.tag,小写连大写
a为小写=>满足a.tag != b.tag,筛选出a小写,b大写tag
如果小写的cnt<3则返回a小写,b大写的结果=>c++,对应C++结果,
总叙述,统计所有tag,如果小写tag未完成数量<3,如果对应大写tag未完成数量不相同,那么
输出小写tag+大写Tagh对应的完成数量
cC ↩︎