Home Database Mysql Tutorial MySQL分组排序取前N条记录以及生成自动数字序列--groupby后limit外加rownumber_MySQL

MySQL分组排序取前N条记录以及生成自动数字序列--groupby后limit外加rownumber_MySQL

Jun 01, 2016 pm 01:02 PM
sequence number

同事提了一个需求,要求按照某列分组,然后将各组的前几条抽取出来。

表结构

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
Copy after login
tudou@b2c.xiaomi.com

原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;
Copy after login
只要将 1 改成N就变成取每组的前N条,因为我极其不喜欢子查询,就改就尝试改称join 的方式。

不过这里需要对所有数据进行排序才能确定每组的前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 |
+--------+---------------+------------+--------+
Copy after login
rownumber已经出来了,再加一个@mid来进行分组
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  |
+--------+---------------+------------+--------+---------------+
Copy after login
好了,再外面加一层inner join 再对 rownumber 做限制 就可以拿到目标数据了。

tudou@b2c.xiaomi.com

set @row=0;set @mid=&#39;&#39;;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 |
+------------+---------------+--------+------------+-----------+--------+
Copy after login
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

iOS 17: How to change iPhone clock style in standby mode 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 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 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

Find numbers that are not divisible by any number in a range, using C++ 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

Use C++ to write code to find the Nth non-square number 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,

Numbers in Java (with 0 prefix and strings) 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

Check if it is a number using is_numeric() function in PHP 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

Represent a number as the sum of the largest possible number of prime numbers in C++ 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

See all articles