SQL查询之分组后取每组的前N条记录_sql分组取每组前几条_瘦子没有夏天的博客

SQL分组查询后取每组的前N条记录

分组查询是常见的SQL查询语句。首先，我们知道MySQL数据库分组功能主要是通过GROUP BY关键字来实现的，而且GROUP BY通常得配合聚合函数来使用用，
比如说分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）等。但是今天我们要探讨的不是GROUP BY关键字学习和使用，而是一种有点另类的“分组”查询。

最近，项目上遇到这样一个功能需求。系统中存在资讯信息这样一个功能模块，用于发布一些和业务相关的活动动态，其中每条资讯信息都有一个所属类型（如科技类的资讯、娱乐类、军事类···）和浏览量字段。
而业务系统的官网上需要滚动展示一些热门资讯信息列表（浏览量越大代表越热门），而且每个类别的相关资讯记录至多显示3条，换句话：“按照资讯分类分组，取每组的前3条资讯信息列表”。后面在尝试GROUP BY
使用的各种方式都不能实现，最后在查阅相关资料后找到了实现的解决方法。

下面，我将模拟一些实际的测试数据重现问题的解决过程。

一、数据准备

数据库

MySQL 8.0社区版

表设计

资讯分类表

id	主键
name	分类名称

资讯信息记录表

code	说明
id	主键
title	资讯名称
views	浏览量
info_type_id	资讯类别

初始化SQL语句

DROP TABLE IF EXISTS `info`;
CREATE TABLE `info`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `views` int(255) DEFAULT NULL,
  `info_type_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of info
-- ----------------------------
INSERT INTO `info` VALUES (1, '中日海军演习', 10, 4);
INSERT INTO `info` VALUES (2, '美俄军事竞赛', 22, 4);
INSERT INTO `info` VALUES (3, '流浪地球电影大火', 188, 1);
INSERT INTO `info` VALUES (4, '葛优瘫', 99, 2);
INSERT INTO `info` VALUES (5, '周杰伦出轨了', 877, 2);
INSERT INTO `info` VALUES (6, '蔡依林西安演唱会', 86, 1);
INSERT INTO `info` VALUES (7, '中纪委调盐', 67, 3);
INSERT INTO `info` VALUES (8, '人民大会堂', 109, 3);
INSERT INTO `info` VALUES (9, '重庆称为网红城市', 202, 1);
INSERT INTO `info` VALUES (10, '胡歌结婚了', 300, 2);
INSERT INTO `info` VALUES (11, 'ipone15马上上市', 678, 2);
INSERT INTO `info` VALUES (12, '中国探月成功', 54, 4);
INSERT INTO `info` VALUES (13, '钓鱼岛对峙', 67, 4);
DROP TABLE IF EXISTS `info_type`;
CREATE TABLE `info_type`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of info_type
-- ----------------------------
INSERT INTO `info_type` VALUES (1, '娱乐');
INSERT INTO `info_type` VALUES (2, '八卦');
INSERT INTO `info_type` VALUES (3, '政治');
INSERT INTO `info_type` VALUES (4, '军事');

资讯分类示例数据如下：

资讯信息记录表示例数据如下：

需求

取热门的资讯信息列表且每个类别只取前3条。

二、核心思想

一般意义上我们在取前N条记录时候，都是根据某个业务字段进行降序排序，然后取前N条就能实现。形如“select * from info order by views asc limit 0,3 ”，这条SQL就是取info表中的前3条记录。但是当你仔细阅读我们的题目要求，你会发现：“它是让你每个类型下都要取浏览量的前3条记录”。一种比较简单但是粗暴的方式就是在Java代码中循环所有的资讯类型，取出每个类型的前3条记录，最后进行汇总。虽然这种方式也能实现我们的要求，但存在很严重的弊端，有可能会发送多次（夸张的说成百上千次也是有可能）sql语句，这种程序显然是有重大缺陷的。

但是，我们换一种思路。我们想在查询每条资讯记录时要是能查出其所在类型的排名就好了，然后根据排名字段进行过滤就好了。这时候我们就想到了子查询，而且MySQL是可以实现这样的功能子查询的。要计算出某条资讯信息的在同资讯分类下所有记录中排第几名，换成算出 有多少条浏览量比当前记录的浏览量高，然后根据具体的多少（N）条+1就是N+1就是当前记录所在其分类下的的排名。

假如以本文上面的示例数据说明：就是在计算每个资讯信息记录时，多计算出一列作为其“排名”字段，然后取“排名”字段的小于等于3的记录即可。如果这里还不是很理解的话，就先看下面的SQL,然后根据SQL再回过头来理解这段话。

三、SQL实现

SQL语句
SELECT t.* from ( SELECT t1.*, (SELECT count(*) + 1 FROM info t2 WHERE t2.info_type_id = t1.info_type_id AND t2.views > t1.views ) top FROM info t1 ) t where top <=3 order by t.info_type_id,top
查询结果
说明
分析top字段的子查询，发现其满足条件有两个：其一是info_type_id和当前记录的type_id相等；其二是info表所有记录大于
当前记录的浏览量且info_type_id相等的记录数量（假设为N），所有N+1就等于当前记录在其分类下的按照浏览量降序排名。

方法二

SQL语句

	SELECT
	info t1
	where (SELECT count(*) + 1 FROM info t2 WHERE t2.info_type_id = t1.info_type_id AND t2.views > t1.views ) <=3
	ORDER BY t1.info_type_id

查询结果
说明
方法二可以看做是方法一的变体

方法三

SQL语句

	SELECT
	info t1
	where exists (SELECT count(*) + 1 FROM info t2 WHERE t2.info_type_id = t1.info_type_id AND t2.views > t1.views having (count(*) + 1) <= 3) 
	ORDER BY t1.info_type_id
其实，有时候在面临业务难题的时候，困难的地方往往不在技术本身，而在于我们解决问题的思维方式。
 就正如案例中求记录的所在分类的排名,把其对等的“转换成有多少条同类别的记录的浏览量比当前记录的大（count聚合函数）”
 问题马上就迎刃而解了。
                    SQL查询之分组后取每组的前N条记录一、背景资讯信息实体code说明id主键title资讯名称views浏览量info_type资讯类别资讯分类实体id主键name资讯分类名称初始化SQLDROP TABLE IF EXISTS `info`;CREATE TABLE `info`  (  `...
[比当前记录val大的条数]小于2条；即当前记录为为分组中的前两条
1 select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val;
2 select a.* from ...
				本文来自 http://www.jb51.net/article/31590.htm
有些语句sql top n 是sqlserver语法
--按某一字段分组取最大(小)值所在行的数据
代码如下:
数据如下： 
name val memo 
a 2 a2(a的第二个值) 
a 1 a1--a的第一个值 
a 3 a3:a的第三个值 
b 1 b1--b的...
				分组查询是常见的SQL查询语句。首先，我们知道MySQL数据库分组功能主要是通过GROUP BY关键字来实现的，而且GROUP BY通常得配合聚合函数来使用用，比如说分组之后你可以计数（COUNT），求和（SUM），求平均数（AVG）等。但是今天我们要探讨的不是GROUP BY关键字学习和使用，而是一种有点另类的“分组”查询。
最近，项目上遇到这样一个功能需求。系统中存在资讯信息这样一个功能模块...
SELECT * FROM tableName a  WHERE
(SELECT COUNT(*) FROM tableName b WHERE b.id=a.id AND b.cnt&gt;a.cnt) &lt; 3
ORDER BY a.id,a.account DESC
where中的select是保证：...
    school_name,
    team_name,
    team_total as tearm_total,substring(team_create_time,1,10) as team_create_time
       /*子查询中为去重复数据，不然...
				一.基本概念关于sql语句中的连接（join）关键字，是较为常用而又不太容易理解的关键字，下面这个例子给出了一个简单的解释 –建表user1,user2： 
table1 : create table user2(id int, user_name varchar(10), over varchar(10)); 
insert into user1 values(1, ‘tangseng’, ‘dt
				可以使用Spark SQL中的窗口函数来实现SQL中的GROUP BY后取组内TOP N。具体步骤如下：
1. 使用Spark SQL中的窗口函数ROW_NUMBER()来为每个分组内的记录编号，按照需要排序的字段进行排序。
2. 将每个分组内的记录按照ROW_NUMBER()的编号进行筛选，选取前N条记录作为TOP N。
3. 将筛选后的结果进行汇总，得到每个分组内的TOP N记录。
示例代码如下：
```scala
import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions.row_number
val df = Seq(
  ("A", 1),
  ("A", 2),
  ("A", 3),
  ("B", 4),
  ("B", 5),
  ("B", 6)
).toDF("group", "value")
val windowSpec = Window.partitionBy("group").orderBy($"value".desc)
val topN = 2
val result = df
  .withColumn("row_number", row_number().over(windowSpec))
  .where($"row_number" <= topN)
  .drop("row_number")
  .orderBy("group", "value")
result.show()
输出结果为：
+-----+-----+
|group|value|
+-----+-----+
|    A|    3|
|    A|    2|
|    B|    6|
|    B|    5|
+-----+-----+
以上代码实现了对每个分组内按照value字段降序排序，选取前2条记录作为TOP N的操作。