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

AI Hentai Generator
Generate AI Hentai for free.

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

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

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

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

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

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,

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

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

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
