MySQL之伪列实现与实践

-------------------------------------------------------------------------------------------------正文--------------------------------------------------------------------------------------------------------------
问题来源：基情
问题描述：看图说明一切

建表语句与模板数据：

点击(此处)折叠或打开

1. CREATE TABLE `tb_score` (
2. `id` bigint(20) NOT NULL AUTO_INCREMENT ,
3. `country` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
4. `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
5. `score` double NULL DEFAULT NULL ,
6. PRIMARY KEY (`id`)
7. )
8. ENGINE=InnoDB
9. DEFAULT CHARACTER SET=latin1 COLLATE=latin1_swedish_ci
10. AUTO_INCREMENT=20
11. ROW_FORMAT=COMPACT
12. ;
13. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (1, '中国', '张三', 81);
14. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (2, '美国', 'Tom', 78);
15. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (3, '英国', 'James', 67.5);
16. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (4, '澳大利亚', 'Jack', 81);
17. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (5, '澳大利亚', 'Roby', 64);
18. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (6, '美国', 'Jory', 69);
19. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (7, '中国', '李四', 92);
20. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (8, '中国', '李天', 82);
21. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (9, '中国', '王智', 71);
22. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (10, '中国', '杨彦', 68.5);
23. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (11, '澳大利亚', 'Jimmy', 92);
24. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (12, '美国', 'Will', 81.5);
25. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (13, '美国', 'Smirth', 79.5);
26. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (14, '英国', 'Toki', 66);
27. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (15, '澳大利亚', 'Kate', 89);
28. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (16, '澳大利亚', 'Mercy', 88);
29. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (17, '美国', 'Lance', 84.5);
30. INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (18, '英国', 'Bandy', 77);

实践环境：MySQL-5.7.7-rc

实践过程：
首先看到这个需求，第一时间想到的就是Oracle的分组统计分析，当然了，MySQL没有，所以先生成了数据，直观的看一看效果：

总共四组，每一组都要选取成绩最好的前N(N=3)

直观的看，已有的ID，country，name，socre都无法作为where的条件去筛选出score最高的三个

那么跳出这些实际的数据，如果以上图这种顺序的数据结构为基础，要实现这个需求的话，

可以用这样子的抽象描述来表达实际需求的意思：以country的值为分组条件，每一组选第一行数据，第二行数据，...，第N行数据

那么在这种抽象描述里面，where的条件就可以做出来：每一组选第一行数据，第二行数据，...，第N行数据

so，可行方法就得出来了：以country的值为分组条件，构建伪列，最终结果筛选前N行数据，伪列值<=N(N=3)

问题来了，MySQL的伪列怎么构造？

构造位列的思想也是靠自连接来完成，使用count(*)来充当伪列的计数器，然后附加上计数的规则，
简单的构造示例：

点击(此处)折叠或打开

1. select t1.*,
2. (select count(*) from tb where id<=t1.id) as rownum
3. from tb t1;

那么在这次的问题里面，这个伪列是有前提条件的：

1.以country的值为分组条件，那么显然，在构造伪列的where条件里面，我们必须限定这个伪列的count(*)所在的范围必须是在同一个country里面，

添加clause1：连接条件为country

2.要选取分数最高的N(N=3)个，所以生成这个伪列的序列号的count(*)的计算方式，也是一个限制条件，

之前的clause1已经把范围限定在了同一个country，那么取分数最高，无非就是算一下比其他低的有多少，

比如以中国为例，想要最高分92的行作为伪列的第一行，代表着，92应该是<=(country=中国)的score的count

所以添加clause2：内表.score<=外表.score

分析完伪列构造的条件，那么就来看看实际构造的效果：

鼓掌撒花~按照每一个country为一组，根据score的大小成功构建了伪列

剩下，各位应该都知道怎么做了~加上 where rownum <=3即可~

最后贴上未经优化的强迫症SQL
点击(此处)折叠或打开

1. select t3.id,t3.country,t3.score
2. from (select t1.*, (select count(*) from tb_score t2 where t1.score<=t2.score and t1.country=t2.country) as rownum
3. from tb_score t1) t3
4. where rownum <=3 order by country,score DESC;

实际上MySQL对这个渣语句已经进行了优化：

目测是独立子查询往上提，果不其然：

------------------------------------------------------------------------------------------------全文完-----------------------------------------------------------------------------------------------------------

PS：伪列不错，灵活使用能够解决很多奇奇怪怪的需求~