mysql 构建多条伪数据 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. 
14.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (1, '中国', '张三', 81); 
15.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (2, '美国', 'Tom', 78); 
16.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (3, '英国', 'James', 67.5); 
17.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (4, '澳大利亚', 'Jack', 81); 
18.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (5, '澳大利亚', 'Roby', 64); 
19.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (6, '美国', 'Jory', 69); 
20.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (7, '中国', '李四', 92); 
21.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (8, '中国', '李天', 82); 
22.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (9, '中国', '王智', 71); 
23.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (10, '中国', '杨彦', 68.5); 
24.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (11, '澳大利亚', 'Jimmy', 92); 
25.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (12, '美国', 'Will', 81.5); 
26.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (13, '美国', 'Smirth', 79.5); 
27.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (14, '英国', 'Toki', 66); 
28.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (15, '澳大利亚', 'Kate', 89); 
29.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (16, '澳大利亚', 'Mercy', 88); 
30.  INSERT INTO `tb_score` (`id`, `country`, `name`, `score`) VALUES (17, '美国', 'Lance', 84.5); 
31.  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：伪列不错，灵活使用能够解决很多奇奇怪怪的需求~