MySQL分组排序取前N条记录以及生成自动数字序列--groupby后limit外加rownumber_MySQL
同事提了一个需求，要求按照某列分组，然后将各组的前几条抽取出来。
表结构
CREATE TABLE `total_freq_ctrl` ( `time` int(10) unsigned NOT NULL, `machine` char(64) NOT NULL, `module` char(32) NOT NULL, `total_flow` int(10) unsigned NOT NULL, `deny_flow` int(10) unsigned NOT NULL, PRIMARY KEY (`module`,`machine`,`time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
原sql
SELECT machine, deny_flow, total_flow, time FROM total_freq_ctrl A WHERE 1 > (SELECT COUNT(machine) FROM total_freq_ctrl WHERE machine = A.machine AND time > A.time) AND A.module = 'all' ORDER BY A.time desc;
不过这里需要对所有数据进行排序才能确定每组的前N条，所以最佳优化也要全表扫描一次。
首先我要对表中数据进行排序，引入一个变量@row来做rownumber
set @row=0;set @mid='';SELECT module, machine, time, @row:=@row+1 rownum FROM total_freq_ctrl order by module,machine,time desc limit 10; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +--------+---------------+------------+--------+ | module | machine | time | rownum | +--------+---------------+------------+--------+ | all | 10.201.20.181 | 1409640060 | 1 | | all | 10.201.20.181 | 1409640000 | 2 | | all | 10.201.20.181 | 1409639940 | 3 | | all | 10.201.20.181 | 1409639880 | 4 | | all | 10.201.20.97 | 1409640060 | 5 | | all | 10.201.20.97 | 1409640000 | 6 | | all | 10.201.20.97 | 1409639940 | 7 | | all | 10.201.20.97 | 1409639880 | 8 | | all | 10.201.20.98 | 1409640060 | 9 | | all | 10.201.20.98 | 1409640000 | 10 | +--------+---------------+------------+--------+
set @row=0;set @mid='';SELECT module, machine, time,case when @mid = machine then @row:=@row+1 else @row:=1 end rownum, @mid:=machine FROM total_freq_ctrl order by module,machine,time desc limit 20; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +--------+---------------+------------+--------+---------------+ | module | machine | time | rownum | @mid:=machine | +--------+---------------+------------+--------+---------------+ | all | 10.201.20.181 | 1409640180 | 1 | 10.201.20.181 | | all | 10.201.20.181 | 1409640120 | 2 | 10.201.20.181 | | all | 10.201.20.181 | 1409640060 | 3 | 10.201.20.181 | | all | 10.201.20.181 | 1409640000 | 4 | 10.201.20.181 | | all | 10.201.20.181 | 1409639940 | 5 | 10.201.20.181 | | all | 10.201.20.181 | 1409639880 | 6 | 10.201.20.181 | | all | 10.201.20.97 | 1409640180 | 1 | 10.201.20.97 | | all | 10.201.20.97 | 1409640120 | 2 | 10.201.20.97 | | all | 10.201.20.97 | 1409640060 | 3 | 10.201.20.97 | | all | 10.201.20.97 | 1409640000 | 4 | 10.201.20.97 | | all | 10.201.20.97 | 1409639940 | 5 | 10.201.20.97 | | all | 10.201.20.97 | 1409639880 | 6 | 10.201.20.97 | | all | 10.201.20.98 | 1409640180 | 1 | 10.201.20.98 | | all | 10.201.20.98 | 1409640120 | 2 | 10.201.20.98 | | all | 10.201.20.98 | 1409640060 | 3 | 10.201.20.98 | | all | 10.201.20.98 | 1409640000 | 4 | 10.201.20.98 | | all | 10.201.20.98 | 1409639940 | 5 | 10.201.20.98 | | all | 10.201.20.98 | 1409639880 | 6 | 10.201.20.98 | +--------+---------------+------------+--------+---------------+
tudou@b2c.xiaomi.com
set @row=0;set @mid='';select a.*,b.rownum from total_freq_ctrl a inner join (SELECT module, machine, time, case when @mid = machine then @row:=@row+1 else @row:=1 end rownum, @mid:=machine mid FROM total_freq_ctrl order by module,machine,time desc) b on b.module=a.module and b.machine=a.machine and b.time=a.time where b.rownum<5; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) +------------+---------------+--------+------------+-----------+--------+ | time | machine | module | total_flow | deny_flow | rownum | +------------+---------------+--------+------------+-----------+--------+ | 1409640360 | 10.201.20.181 | all | 53937 | 6058 | 1 | | 1409640300 | 10.201.20.181 | all | 52588 | 5701 | 2 | | 1409640240 | 10.201.20.181 | all | 54254 | 5608 | 3 | | 1409640180 | 10.201.20.181 | all | 54684 | 5811 | 4 | | 1409640360 | 10.201.20.97 | all | 50679 | 5307 | 1 | | 1409640300 | 10.201.20.97 | all | 50472 | 5239 | 2 | | 1409640240 | 10.201.20.97 | all | 51586 | 5509 | 3 | | 1409640180 | 10.201.20.97 | all | 50794 | 5378 | 4 | | 1409640360 | 10.201.20.98 | all | 84747 | 5652 | 1 | | 1409640300 | 10.201.20.98 | all | 84506 | 5696 | 2 | | 1409640240 | 10.201.20.98 | all | 84982 | 5513 | 3 | | 1409640180 | 10.201.20.98 | all | 83997 | 5623 | 4 | +------------+---------------+--------+------------+-----------+--------+
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
热门话题
iOS 17：如何在待机模式下更改iPhone时钟样式
Sep 10, 2023 pm 09:21 PM
待机是一种锁定屏幕模式，当iPhone插入充电器并以水平（或横向）方向定位时激活。它由三个不同的屏幕组成，其中一个是全屏时间显示。继续阅读以了解如何更改时钟的样式。StandBy的第三个屏幕显示各种主题的时间和日期，您可以垂直滑动。某些主题还会显示其他信息，例如温度或下一个闹钟。如果您按住任何时钟，则可以在不同的主题之间切换，包括数字、模拟、世界、太阳能和浮动。Float以可自定义的颜色以大气泡数字显示时间，Solar具有更多标准字体，具有不同颜色的太阳耀斑设计，而World则通过突出显示世界地
JavaScript中生成随机数字和字符串
Sep 02, 2023 am 08:57 AM
生成随机数或字母数字字符串的能力在许多情况下都会派上用场。您可以使用它在游戏中的不同位置生成敌人或食物。您还可以使用它向用户建议随机密码或创建文件名来保存文件。我写了一篇关于如何在PHP中生成随机字母数字字符串的教程。我在这篇文章的开头说，几乎没有事件是真正随机的，同样的情况也适用于随机数或字符串生成。在本教程中，我将向您展示如何在JavaScript中生成伪随机字母数字字符串。在JavaScript中生成随机数让我们从生成随机数开始。我想到的第一个方法是Math.random()，它返回一个浮
C++程序将一个数字四舍五入到n位小数
Sep 12, 2023 pm 05:13 PM
在任何语言中编写程序时，将数字表示为输出是一项有趣且重要的任务。对于整数类型（short、long或medium类型的数据），很容易将数字表示为输出。对于浮点数（float或double类型），有时我们需要将其四舍五入到特定的小数位数。例如，如果我们想将52.24568表示为三位小数，需要进行一些预处理。在本文中，我们将介绍几种技术，通过四舍五入将浮点数表示为特定的小数位数。在不同的方法中，使用类似C的格式化字符串、使用精度参数以及使用数学库中的round()函数是很重要的。让我们逐个来看。带有
使用C++编写代码，找到第N个非平方数
Aug 30, 2023 pm 10:41 PM
我们都知道不是任何数字的平方的数字，如2、3、5、7、8等。非平方数有N个，不可能知道每个数字。因此，在本文中，我们将解释有关无平方数或非平方数的所有内容，以及在C++中查找第N个非平方数的方法。第N个非平方数如果一个数是整数的平方，则该数被称为完全平方数。完全平方数的一些例子是-1issquareof14issquareof29issquareof316issquareof425issquareof5如果一个数不是任何整数的平方，则该数被称为非平方数。例如，前15个非平方数是-2,3,5,6,
在PHP中使用is_numeric()函数检查是否为数字
Jun 27, 2023 pm 05:00 PM
在PHP编程语言中，is_numeric()函数是一种非常常用的函数，用于判断一个变量或值是否为数字。在实际编程中，经常需要对用户输入的数值进行验证，判断其是否为数字类型，这时就可以使用is_numeric()函数进行判断。一、is_numeric()函数简介is_numeric()函数是一个用于检测变量或值是否为数字的函数。如果变量或值为数字，则返回tru
找出在范围内不可被任何数整除的数字，使用C++
Sep 13, 2023 pm 09:21 PM
在本文中，我们将讨论查找1到n（给定）之间的数字的问题，这些数字不能被2到10之间的任何数字整除。让我们通过一些例子来理解这一点-Input:num=14Output:3Explanation:Therearethreenumbers,1,11,and13,whicharenotdivisible.Input:num=21Output:5Explanation:Therearefivenumbers1,11,13,17,and19,whicharenotdivisible.求解的方法简单方法如果
Java中的数字（带有0前缀和字符串）
Aug 29, 2023 pm 01:45 PM
Java中的数字重要的是要理解数字类不是一个有形的类，而是一个抽象的类。在它内部，我们有一组定义其功能的包装类。这些包装类包括Integer、Byte、Double、Short、Float和Long。您可能会注意到，这些与我们之前讨论的基本数据类型相同，但它们表示为具有大写名称的单独类，以符合类命名约定。根据特定函数或程序范围的要求，编译器自动将原始数据类型转换为对象，反之亦然，并且数字类是java.lang包的一部分。此过程称为自动装箱和拆箱。通过掌握数字类及其对应的包装类的抽象性质，我们可以
用C++将一个数字表示为最大可能数量的质数之和
Aug 31, 2023 pm 04:29 PM
讨论一个问题，例如，给定一个数字N，我们需要将该数字拆分为最大素数和Input:N=7Output:223Explanation:7canberepresentedasthesumoftwo2’sanda3whicharethemaximumpossibleprimenumbers.Input:N=17Output:22222223求解方法为了用素数表示一个数，我们可以用N减去一个素数，然后检查素数的差异。如果差是素数，那么我们可以将N表示为两个素数之和。但是在这里，我们必须
