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 | +------------+---------------+--------+------------+-----------+--------+
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1386
52
iOS 17: How to change iPhone clock style in standby mode
Sep 10, 2023 pm 09:21 PM
Standby is a lock screen mode that activates when the iPhone is plugged into the charger and oriented in horizontal (or landscape) orientation. It consists of three different screens, one of which is displayed full screen time. Read on to learn how to change the style of your clock. StandBy's third screen displays times and dates in various themes that you can swipe vertically. Some themes also display additional information, such as temperature or next alarm. If you hold down any clock, you can switch between different themes, including Digital, Analog, World, Solar, and Floating. Float displays the time in large bubble numbers in customizable colors, Solar has a more standard font with a sun flare design in different colors, and World displays the world by highlighting
Generate random numbers and strings in JavaScript
Sep 02, 2023 am 08:57 AM
The ability to generate random numbers or alphanumeric strings comes in handy in many situations. You can use it to spawn enemies or food at different locations in the game. You can also use it to suggest random passwords to users or create filenames to save files. I wrote a tutorial on how to generate random alphanumeric strings in PHP. I said at the beginning of this post that few events are truly random, and the same applies to random number or string generation. In this tutorial, I'll show you how to generate a pseudo-random alphanumeric string in JavaScript. Generating Random Numbers in JavaScript Let’s start by generating random numbers. The first method that comes to mind is Math.random(), which returns a float
C++ program to round a number to n decimal places
Sep 12, 2023 pm 05:13 PM
Representing numbers as output is an interesting and important task when writing a program in any language. For integer types (data of type short, long, or medium), it is easy to represent numbers as output. For floating point numbers (float or double type), sometimes we need to round them to a specific number of decimal places. For example, if we want to represent 52.24568 as three decimal places, some preprocessing is required. In this article, we will introduce several techniques to represent floating point numbers to a specific number of decimal places by rounding. Among the different approaches, it is important to use a C-like format string, use the precision argument, and use the round() function from the math library. Let’s look at them one by one. with
Use C++ to write code to find the Nth non-square number
Aug 30, 2023 pm 10:41 PM
We all know numbers that are not the square of any number, such as 2, 3, 5, 7, 8, etc. There are N non-square numbers, and it is impossible to know every number. So, in this article, we will explain everything about squareless or non-square numbers and ways to find the Nth non-square number in C++. Nth non-square number If a number is the square of an integer, then the number is called a perfect square. Some examples of perfect square numbers are -1issquareof14issquareof29issquareof316issquareof425issquareof5 If a number is not the square of any integer, then the number is called non-square. For example, the first 15 non-square numbers are -2,3,5,6,
Check if it is a number using is_numeric() function in PHP
Jun 27, 2023 pm 05:00 PM
In the PHP programming language, the is_numeric() function is a very commonly used function, used to determine whether a variable or value is a number. In actual programming, it is often necessary to verify the value entered by the user to determine whether it is a numeric type. In this case, the is_numeric() function can be used to determine. 1. Introduction to is_numeric() function The is_numeric() function is a function used to detect whether a variable or value is a number. Returns tru if the variable or value is a number
Find numbers that are not divisible by any number in a range, using C++
Sep 13, 2023 pm 09:21 PM
In this article, we will discuss the problem of finding numbers between 1 and n (given) that are not divisible by any number between 2 and 10. Let us understand this with some examples - Input:num=14Output:3Explanation:Therearethreenumbers,1,11,and13,whicharenotdivisible.Input:num=21Output:5Explanation:Therearefivenumbers1,11,13,17,and19,whicharenotdivisible. Solved Simple method if
Numbers in Java (with 0 prefix and strings)
Aug 29, 2023 pm 01:45 PM
Numbers in Java It is important to understand that the number class is not a tangible class but an abstract class. Inside it, we have a set of wrapper classes that define its functionality. These wrapper classes include Integer, Byte, Double, Short, Float, and Long. You may notice that these are the same basic data types we discussed earlier, but they are represented as separate classes with uppercase names to conform to the class naming convention. The compiler automatically converts primitive data types to objects and vice versa as required for a particular function or program scope, and numeric classes are part of the java.lang package. This process is called autoboxing and unboxing. By grasping the abstract nature of numeric classes and their corresponding wrapper classes, we can
Represent a number as the sum of the largest possible number of prime numbers in C++
Aug 31, 2023 pm 04:29 PM
Discuss a problem, for example, given a number N, we need to split the number into its maximum prime numbers. , we can subtract a prime number from N and then check the difference in prime numbers. If the difference is a prime number, then we can express N as the sum of two prime numbers. But here we have to
