sql_mode 不能包含 ONLY_FULL_GROUP_BY
。比如设置成:set session sql_mode = "TRADITIONAL";
MySQL 中的 temporary table 不能 reopen,也就不支持连接自身,所以本文中的临时表指的是临时用的普通表
发贴表:
用户 | 标题 |
用户1 | 标题1 |
用户1 | 标题2 |
用户2 | 标题3 |
用户3 | 标题4 |
用户3 | 标题5 |
用户1 | 标题6 |
用户3 | 标题7 |
用户2 | 标题8 |
用户2 | 标题9 |
用户4 | 标题10 |
用户4 | 标题11 |
用户5 | 标题12 |
求发贴数量前 N 的用户。
比如当 N = 2 时,因为用户 1、2、3 都发了 3 篇贴,用户 4 发了 2 篇贴,用户 5 发了 1 篇贴,所以结果集应该为:
用户 | 数量 |
用户1 | 3 |
用户2 | 3 |
用户3 | 3 |
如果直接执行:
select 用户, count(*) as 数量 from 发贴表 group by 用户 order by 数量 desc limit 2;
那么将得到结果集:
用户 | 数量 |
用户1 | 3 |
用户2 | 3 |
因此应该采用如下方案:
第一步:
-- 获取每个用户的发贴数量 select 用户, count(*) as 数量 from 发贴表 group by 用户;
得到临时表:
用户 | 数量 |
用户1 | 3 |
用户2 | 3 |
用户3 | 3 |
用户4 | 2 |
用户5 | 1 |
当 N = 1 时,即求第一名的 SQL 是:
select t1.用户, t1.数量 from temp t1 left join temp t2 on t1.数量 < t2.数量 where t2.数量 is null;
结果集是:
用户 | 数量 |
用户1 | 3 |
用户2 | 3 |
用户3 | 3 |
当 N > 1 时,执行下面的流程:
第二步:
-- 获取比当前用户发贴量多的用户 select t1.用户, t1.数量 from 临时表 t1 left join 临时表 t2 on t1.数量 < t2.数量;
得到临时表2:
用户 | 数量 |
用户1 | 3 |
用户2 | 3 |
用户3 | 3 |
用户4 | 2 |
用户4 | 2 |
用户4 | 2 |
用户5 | 1 |
用户5 | 1 |
用户5 | 1 |
用户5 | 1 |
第三步:
-- 获取 top N select 用户, 数量 from 临时表 group by 用户 having count(*) < 2;
得到结果集:
用户 | 数量 |
用户1 | 3 |
用户2 | 3 |
用户3 | 3 |
其中第一步可以使用临时表,第二、三步结合起来:
select 用户, 数量 from (select t1.用户, t1.数量 from 临时表 t1 left join 临时表 t2 on t1.数量 < t2.数量) t group by 用户 having count(*) < 2;
成绩表:
课程名 | 学生姓名 | 成绩 |
课程1 | 学生1 | 100 |
课程1 | 学生2 | 90 |
课程1 | 学生3 | 95 |
课程2 | 学生1 | 90 |
课程2 | 学生2 | 100 |
课程2 | 学生3 | 95 |
求每科的前 N 名。
当 N = 2 时,结果集应该为:
课程名 | 学生姓名 | 成绩 |
课程1 | 学生1 | 100 |
课程1 | 学生3 | 95 |
课程2 | 学生2 | 100 |
课程2 | 学生3 | 95 |
当 N = 1 时,即求每门课程的第一名的 SQL 是:
select t1.课程名, t1.学生姓名, t1.成绩 from 成绩表 t1 left join 成绩表 t2 on t1.课程名 = t2.课程名 and t1.成绩 < t2.成绩 where t2.成绩 is null;
当 N > 1 时,执行下面的流程:
第一步:
-- 找出比当前学生成绩高的学生 select t1.课程名, t1.学生姓名, t1.成绩 from 成绩表 t1 left join 成绩表 t2 on t1.课程名 = t2.课程名 and t1.成绩 < t2.成绩;
得到结果集:
课程名 | 学生姓名 | 成绩 |
课程1 | 学生1 | 100 |
课程1 | 学生2 | 90 |
课程1 | 学生2 | 90 |
课程1 | 学生3 | 95 |
课程2 | 学生1 | 90 |
课程2 | 学生1 | 90 |
课程2 | 学生2 | 100 |
课程2 | 学生3 | 95 |
第二步:
-- 获取 top N select 课程名, 学生姓名, 成绩 from 临时表 group by 课程名, 学生姓名 having count(*) < 2;
得到最终结果:
课程号 | 学生姓名 | 成绩 |
课程1 | 学生1 | 100 |
课程1 | 学生3 | 95 |
课程2 | 学生2 | 100 |
课程2 | 学生3 | 95 |
将第一、二步结合起来,得到:
select 课程名, 学生姓名, 成绩 from (select t1.课程名, t1.学生姓名, t1.成绩 from 成绩表 t1 left join 成绩表 t2 on t1.课程名 = t2.课程名 and t1.成绩 < t2.成绩) t group by 课程名, 学生姓名 having count(*) < 2;