摘要：MySQL 之伪列实现(附实例说明)，按国家查出分数在前三名的person
需求：按国家查出分数在前三名的person。
一、创建数据，直接执行下面数据即可
-- MySQL dump 10.13 Distrib 5.7.21, for Linux (x86_64)
--
-- Host: localhost Database: test
-- ------------------------------------------------------
-- Server version 5.7.21-0ubuntu0.16.04.1
--
-- Table structure for table `tb_country`
--
DROP TABLE IF EXISTS `tb_country`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tb_country` (
`id` int(11) NOT NULL,
`country` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`score` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `tb_country`
--
LOCK TABLES `tb_country` WRITE;
/*!40000 ALTER TABLE `tb_country` DISABLE KEYS */;
INSERT INTO `tb_country` VALUES (1,'中国','张三','81'),(2,'美国','Tom','78'),(3,'英国','James','67.5'),(4,'澳大利亚','Jack','81'),(5,'澳大利亚','Roby','64'),(6,'美国','Jory','69'),(7,'中国','李四','92'),(8,'中国','李天','82'),(9,'中国','王智','71'),(10,'中国','杨彦','68.5'),(11,'澳大利亚','Jimmy','92'),(12,'美国','Will','81.5'),(13,'美国','Smirth','79.5'),(14,'英国','Toki','66'),(15,'澳大利亚','Kate','89'),(16,'澳大利亚','Mercy','88'),(17,'美国','Lance','84.5'),(18,'英国','Bandy','77');
/*!40000 ALTER TABLE `tb_country` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
-- Dump completed on 2018-05-11 13:49:07二、首先看到这个需求，第一时间想到的就是Oracle的分组统计分析，当然了，MySQL没有，所以先生成了数据，直观的看一看效果：
select * from tb_country order by country ,score desc;
三、总共四组，每一组都要选取成绩最好的前N(N=3)
直观的看，已有的ID，country，name，socre都无法作为where的条件去筛选出score最高的三个
那么跳出这些实际的数据，如果以上图这种顺序的数据结构为基础，要实现这个需求的话，
可以用这样子的抽象描述来表达实际需求的意思：以country的值为分组条件，每一组选第一行数据，第二行数据，...，第N行数据;
那么在这种抽象描述里面，where的条件就可以做出来：每一组选第一行数据，第二行数据，...，第N行数据
so，可行方法就得出来了：以country的值为分组条件，构建伪列，最终结果筛选前N行数据，伪列值<=N(N=3)
问题来了，MySQL的伪列怎么构造？
构造位列的思想也是靠自连接来完成，使用count(*)来充当伪列的计数器，然后附加上计数的规则，
简单的构造示例：
#构造伪列
select t1.*,
(select count(*) from tb_country where id<=t1.id) as rownum
from tb_country t1;那么在这次的问题里面，这个伪列是有前提条件的：
1.以country的值为分组条件，那么显然，在构造伪列的where条件里面，我们必须限定这个伪列的count(*)所在的范围必须是在同一个country里面，
添加Subquery1：连接条件为country
2.要选取分数最高的N(N=3)个，所以生成这个伪列的序列号的count(*)的计算方式，也是一个限制条件，
之前的Subquery1已经把范围限定在了同一个country，那么取分数最高，无非就是算一下比其他低的有多少，
比如以中国为例，想要最高分92的行作为伪列的第一行，代表着，92应该是<=(country=中国)的score的count
所以添加Subquery2：内表.score<=外表.score
分析完伪列构造的条件，那么就来看看实际构造的效果：
#进行排序
select t1.*,
(select count(*) from tb_country t2 where t1.score<=t2.score and t1.country=t2.country) as rownum
from tb_country t1
order by t1.country,t1.score DESC
按照每一个country为一组，根据score的大小成功构建了伪列
剩下，~加上 where rownum <=3即可~
最后贴上未经优化的强迫症SQL
select t3.id,t3.country,t3.score
from (select t1.*, (select count(*) from tb_country t2 where t1.score<=t2.score and t1.country=t2.country) as rownum
from tb_country t1) t3
where rownum <=3 order by country,score DESC;
有疑问欢迎在下方评论区留言
