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

ホームページ

データベース

mysql チュートリアル

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:02 PM

順序番号

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

表结构

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

ログイン後にコピー

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 = &#39;all&#39; ORDER BY A.time desc;

ログイン後にコピー

只要将 1 改成N就变成取每组的前N条，因为我极其不喜欢子查询，就改就尝试改称join 的方式。

不过这里需要对所有数据进行排序才能确定每组的前N条，所以最佳优化也要全表扫描一次。

首先我要对表中数据进行排序，引入一个变量@row来做rownumber

set @row=0;set @mid=&#39;&#39;;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 |
+--------+---------------+------------+--------+

ログイン後にコピー

rownumber已经出来了，再加一个@mid来进行分组

set @row=0;set @mid=&#39;&#39;;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  |
+--------+---------------+------------+--------+---------------+

ログイン後にコピー

好了，再外面加一层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 |
+------------+---------------+--------+------------+-----------+--------+

ログイン後にコピー

このウェブサイトの声明

この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。

ホットAIツール

ホットツール

ホットトピック

Gmailメールのログイン入り口はどこですか？

7393

Java チュートリアル

1630

CakePHP チュートリアル

1357

Laravel チュートリアル

1268

PHP チュートリアル

1217

Related knowledge

iOS 17: スタンバイモードでiPhoneの時計スタイルを変更する方法 Sep 10, 2023 pm 09:21 PM

スタンバイは、iPhone が充電器に接続され、水平 (または横) 向きになっているときにアクティブになるロック画面モードです。これは 3 つの異なる画面で構成されており、そのうちの 1 つは全画面表示されます。時計のスタイルを変更する方法については、この記事を読んでください。 StandBy の 3 番目の画面には、垂直にスワイプできるさまざまなテーマで時刻と日付が表示されます。一部のテーマでは、温度や次のアラームなどの追加情報も表示されます。時計を押し続けると、デジタル、アナログ、ワールド、ソーラー、フローティングなどのさまざまなテーマを切り替えることができます。 Float はカスタマイズ可能な色の大きなバブル数字で時間を表示します。Solar はさまざまな色の太陽フレアのデザインを備えたより標準的なフォントを持ち、World は世界を強調表示して表示します。

JavaScript で乱数と文字列を生成する Sep 02, 2023 am 08:57 AM

乱数や英数字の文字列を生成する機能は、多くの状況で役立ちます。これを使用して、ゲーム内のさまざまな場所に敵や食べ物をスポーンできます。これを使用して、ユーザーにランダムなパスワードを提案したり、ファイルを保存するためのファイル名を作成したりすることもできます。 PHP でランダムな英数字文字列を生成する方法に関するチュートリアルを書きました。この投稿の冒頭で、真にランダムなイベントはほとんどないと述べましたが、乱数や文字列の生成にも同じことが当てはまります。このチュートリアルでは、JavaScript で擬似ランダムな英数字文字列を生成する方法を説明します。 JavaScript での乱数の生成まずは乱数を生成してみましょう。最初に思い浮かぶメソッドは Math.random() です。これは浮動小数点を返します。

数値を小数点以下 n 桁に四捨五入する C++ プログラム Sep 12, 2023 pm 05:13 PM

数値を出力として表現することは、どの言語でプログラムを作成する場合でも興味深い、重要な作業です。整数型 (short、long、または Medium 型のデータ) の場合、出力として数値を表すのは簡単です。浮動小数点数 (float または double 型) の場合、特定の小数点以下の桁数に四捨五入する必要がある場合があります。たとえば、52.24568 を小数点以下 3 桁で表現したい場合は、前処理が必要です。この記事では、浮動小数点数を四捨五入して特定の小数点以下の桁数に表現するいくつかのテクニックを紹介します。さまざまなアプローチの中でも、C に似たフォーマット文字列を使用すること、precision 引数を使用すること、および数学ライブラリのround() 関数を使用することが重要です。一つずつ見ていきましょう。と

C++ を使用して、範囲内のどの数値でも割り切れない数値を検索します。 Sep 13, 2023 pm 09:21 PM

この記事では、2 から 10 までのどの数値でも割り切れない 1 から n (指定された) までの数値を見つける問題について説明します。いくつかの例でこれを理解しましょう - 入力:num=14出力:3説明:Therearethreenumbers,1,11,and13,thatarenotdivisible.Input:num=21Output:5説明:Therearefivenumbers1,11,13,17,and19,thatarenotdivisible. 解決済みシンプルなメソッド if

C++ を使用して N 番目の非平方数を見つけるコードを作成する Aug 30, 2023 pm 10:41 PM

私たちは皆、2、3、5、7、8 など、数の 2 乗ではない数字を知っています。非正方形の数は N 個あり、すべての数を知ることは不可能です。そこで、この記事では、平方なしまたは非平方数と、C++ で N 番目の非平方数を見つける方法についてすべて説明します。 N 番目の非平方数数値が整数の 2 乗である場合、その数値は完全平方と呼ばれます。完全平方数の例としては、-1issquareof14issquareof29issquareof316issquareof425issquareof5 などがあります。数値がどの整数の平方でもない場合、その数値は非正方形と呼ばれます。たとえば、最初の 15 個の非平方数は -2、3、5、6 です。

Java の数値 (接頭辞と文字列が 0 の場合) Aug 29, 2023 pm 01:45 PM

Java の数値数値クラスは有形のクラスではなく、抽象クラスであることを理解することが重要です。その中には、その機能を定義する一連のラッパークラスがあります。これらのラッパークラスには、Integer、Byte、Double、Short、Float、Long が含まれます。これらは前に説明したのと同じ基本的なデータ型ですが、クラスの命名規則に準拠するために大文字の名前を持つ別のクラスとして表されていることに気づくでしょう。コンパイラは、特定の関数またはプログラムスコープの必要に応じて、プリミティブデータ型をオブジェクトに、またはその逆に自動的に変換します。数値クラスは java.lang パッケージの一部です。このプロセスは、オートボックス化およびアンボックス化と呼ばれます。数値クラスとそれに対応するラッパークラスの抽象的な性質を理解することで、次のことが可能になります。

PHPのis_numeric()関数を使用して数値かどうかを確認します Jun 27, 2023 pm 05:00 PM

PHP プログラミング言語では、is_numeric() 関数は非常によく使用される関数で、変数または値が数値かどうかを判断するために使用されます。実際のプログラミングでは、ユーザーが入力した値が数値型であるかどうかを検証する必要がある場合が多く、この場合には is_numeric() 関数を使用して判定することができます。 1. is_numeric() 関数の概要 is_numeric() 関数は、変数または値が数値であるかどうかを検出するために使用される関数です。変数または値が数値の場合は true を返します