MySQL サブクエリとグループ化されたクエリ

MySQL サブクエリとグループ化されたクエリ

概要

サブクエリは SQL クエリの重要な部分です。複数のテーブル間でデータを集計および判断する手段であり、複雑なデータの処理を容易にします。このセクションでは、主にサブクエリについて学習します。

まずデータを準備しましょう。ここでは、次の操作のために、クラス、生徒、卒業スコアの 3 つのテーブルを作成します。

`Helenlyn_Class` が存在する場合はデータベースを削除します。
データベース `Helenlyn_Class` を作成します。

/*クラステーブル*/
`classes` が存在する場合はテーブルを削除します。
テーブル「classes」を作成します(
 `classid` int 主キー AUTO_INCREMENT コメント 'クラス ID',
 `classname` varchar(30) DEFAULT NULL コメント 'クラス名'
) ENGINE=InnoDB コメント 'クラス テーブル';

`classes`(`classname`) に挿入します
値('中学校1年生')、('中学校2年生')、('中学校3年生');

/*学生テーブル: ここでは学生IDと名前の両方が一意であると仮定します*/

`students` が存在する場合はテーブルを削除します。
テーブル「学生」を作成(
 `studentid` int 主キー NOT NULL AUTO_INCREMENT コメント '学生ID',
 `studentname` varchar(20) DEFAULT NULL コメント '学生名',
 `score` DECIMAL(10,2) DEFAULT NULL コメント '卒業スコア',
 `classid` int(4) DEFAULT NULL comment 'クラステーブルのクラスIDからのクラスID'
)ENGINE=InnoDB コメント 'student table';
`students`(`studentname`,`score`,`classid`) の値を挿入します
('ブランド',97.5,1),('ヘレン',96.5,1),('リン',96,1),('ソル',97,1),('ウェン',100,1),('ディニー',92.7,1),
('b1',81,2)、('b2',82,2)、('b3',83,2)、('b4',84,2)、('b5',85,2)、('b6',86,2)、
('c1',71,3)、('c2',72.5,3)、('c3',73,3)、('c4',74,3)、('c5',75,3)、('c6',76,3);


/*卒業評価スコアランキング表*/
`scores` が存在する場合はテーブルを削除します。
テーブル「スコア」を作成します(
 `scoregrad` varchar(3) 主キーコメント 'グレード: S、A、B、C、D'、
 `downset` int コメント 'スコア評価下限',
 `upset` int コメント 'スコア評価上限'
)コメント「卒業評価スコアランキング表」
`scores` に値('S'、91、100)、('A'、81、90)、('B'、71、80)、('C'、61、70)、('D'、51、60)を挿入します。

サブクエリ

SQL は、他のクエリ内にネストされたクエリであるサブクエリの作成をサポートしています。つまり、他の選択ステートメントが選択ステートメント内に出現する可能性があり、これをサブクエリまたは内部クエリと呼びます。外部選択ステートメントは、メイン クエリまたは外部クエリと呼ばれます。

サブクエリの分類

クエリの結果によると

1. 単一行および単一列 (スカラー サブクエリ): 単一値データとして理解できる特定の列の内容を返します。

2. 単一行と複数列 (行サブクエリ): データの行内の複数列の内容を返します。

3. 複数行単一列(列サブクエリ):複数行の同じ列の内容を返します。これは、操作範囲を指定するのと同じです。

4. 複数行と複数列 (テーブル サブクエリ): クエリによって返される結果は一時テーブルです。

サブクエリの位置で区別する

選択後のサブクエリ: スカラー サブクエリのみがサポートされます。つまり、データの単一の値のみが返されます。

From 型サブクエリ: 内部クエリの結果は、外部 SQL が再度クエリを実行するための一時テーブルとして使用されるため、テーブル サブクエリがサポートされます。

Where または having サブクエリ: 内部クエリの結果を外部クエリの比較条件として使用することを意味し、スカラー サブクエリ (単一列および単一行)、列サブクエリ (単一列および複数行)、および行サブクエリ (複数列および複数行) をサポートします。

通常、以下の方法と組み合わせて使用​​されます。

1) IN サブクエリ: 内部クエリ ステートメントは 1 つのデータ列のみを返し、このデータ列の値は外部クエリ ステートメントによる比較に使用されます。

2) 任意のサブクエリ: 内部サブクエリ内の比較条件が満たされている限り、結果は外部クエリ条件として返されます。

3) すべてのサブクエリ: 内部サブクエリによって返される結果は、すべての内部クエリ条件を同時に満たす必要があります。

4) 比較演算子サブクエリ: サブクエリで使用できる比較演算子には、>、>=、<=、<、=、<> などがあります。

Exists サブクエリ: 外側のレイヤー (複数の行と列をサポート) のクエリ結果を内側のレイヤーに渡して、内側のレイヤーが確立されているかどうかを確認します。簡単に言うと、外側のレイヤー (つまり、前のステートメント) は、後者が true を返す場合にのみ実行され、それ以外の場合は実行されません。

一つずつテストしてみましょう。

選択後のサブクエリ

これは select の後に配置され、スカラー サブクエリのみをサポートします。つまり、データの単一の値のみを返すことができます。たとえば、上記の学生クラス テーブルでは、次のようにして各クラスの学生数を照会できます。

mysql> クラス番号としてa.classid、クラス名としてa.classnameを選択し、
クラス a の生徒数として (select count(*) from students b where b.classid = a.classid) を指定します。
+----------+----------+----------+
| クラス番号| クラス名| 生徒数|
+----------+----------+----------+
| 1 | 1年生、9年生 | 6 |
| 2 | 2年生、9年生 | 6 |
| 3 | 3年生、9年生 | 6 |
+----------+----------+----------+
3行セット

学生ブランドが属するクラスを照会するには、次のように記述します。

mysql>選択
(a.classid = b.classid かつ b.studentname='brand' のクラス a、生徒 b からクラス名を選択)
クラスとして;
+----------+
| クラス|
+----------+
| クラス 1、グレード 9|
+----------+
セット内の1行

サブクエリの後に

内部クエリの結果は一時テーブルとして扱われ、外部 SQL はテーブル サブクエリをサポートする追加クエリ用に提供されます。ただし、サブクエリにエイリアスを設定する必要があります。そうしないと、テーブルが見つかりません。

各クラスの平均スコアを照会します。

mysql> students から a.classid,avg(a.score) を選択し、 a.classid でグループ化します。

+---------+--------------+
| クラスID | 平均(a.スコア) |
+---------+--------------+
| 1 | 96.616667 |
| 2 | 83.500000 |
| 3 | 73.583333 |
+---------+--------------+
3行セット

卒業評価スコアランキング表を照会します。S から低い順に並べ替えます。

mysql> select * from scores order by upset desc;

+-----------+---------+--------+
| スコアグレード | 落ち込む | 動揺 |
+-----------+---------+--------+
| S | 91 | 100 |
| あ | 81 | 90 |
| B | 71 | 80 |
| C | 61 | 70 |
| D | 51 | 60 |
+-----------+---------+--------+
5行セット

2 つのクエリの結果に基づいて各クラスの平均スコアを調べたい場合は、from の後のサブクエリを使用できます。コードは次のとおりです。

クラスIDとしてa.classid、卒業平均スコアとしてa.avgscore、スコア評価としてb.scoregradを選択します。
(classid、avg(score) を avgscore として、classid で学生グループから選択) として、
スコア b で、a.avgscore は b.downset と b.upset の間です。

+--------+--------------+----------+
| クラス ID | 平均卒業スコア | スコア評価 |
+--------+--------------+----------+
| 1 | 96.616667 | S |
| 2 | 83.500000 | A |
| 3 | 73.583333 | B |
+--------+--------------+----------+
3行セット

サブテーブル クエリの場合、エイリアスを指定する必要があります。指定しない場合は、「すべての派生テーブルには独自のエイリアスが必要です」というメッセージが表示されます。試してみることができます。

Whereとサブクエリ

上で述べたように、where または having の後には、スカラー サブクエリ (単一行および単一列のサブクエリ)、列サブクエリ (単一列および複数行のサブクエリ)、行サブクエリ (複数行および複数列) の 3 つの方法を使用できます。

彼には次のような共通の特徴があります。

1. 一般に、サブクエリは括弧で囲まれます。

2. サブクエリは通常、条件の右側に配置されます。

3. スカラーサブクエリ。通常、単一行演算子、複数行演算子 >、<、>=、<=、=、<> とともに使用されます。

4. 列サブクエリ。通常は複数行演算子とともに使用されます。

5. in、not in、all、any とともに使用します。in はリスト内のいずれかを参照します。any はリスト内のいずれかを比較します。score>any(60,70,80) の場合、score>60 です。all はリスト内のすべてを比較します。score>(60,70,80) の場合、score は>80 である必要があります。

単一スカラーサブクエリアプリケーション

つまり、 or having の後にはスカラー クエリのみが続きます。たとえば、diny (92.7 ポイント) よりも良いスコアを持つ学生をクエリするには、次のようになります。

mysql> select * from students a where a.score >(select b.score from students b where b.studentname='diny');
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 1 | ブランド | 97.5 | 1 |
| 2 | ヘレン | 96.5 | 1 |
| 3 | リン | 96 | 1 |
| 4 | ソル | 97 | 1 |
| 5 | ウェン | 100 | 1 |
+-----------+-------------+--------+---------+
5行セット

複数のスカラーサブクエリアプリケーション

または having の後にはスカラー クエリのみが続きます。たとえば、diny よりもスコアが低い (92.7 ポイント) 学生や、diny と同じクラスではない学生をクエリするには、次のようになります。

mysql> 学生から*を選択
a.score <(b.studentname='diny' の students b から b.score を選択)
かつ a.classid <> (select b.classid from students b where b.studentname='diny') ;
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 9 | b3 | 83 | 2 |
| 10 | b4 | 84 | 2 |
| 11 | b5 | 85 | 2 |
| 12 | b6 | 86 | 2 |
| 13 | c1 | 71 | 3 |
| 14 | c2 | 72.5 | 3 |
| 15 | c3 | 73 | 3 |
| 16 | c4 | 74 | 3 |
| 17 | c5 | 75 | 3 |
| 18 | c6 | 76 | 3 |
+-----------+-------------+--------+---------+
12行セット

サブクエリ + グループ化関数

3つのクラスの平均点をそれぞれ取得し、having式を使用して、学年全体の平均点よりも低い点数のクラス情報をフィルタリングします。

mysql> a.classid、avg(a.score) を a.classid でグループ化した students から avgscore として選択します
平均スコア < (生徒から平均スコアを選択) を持つこと。
+---------+------------+
| クラスID | 平均スコア |
+---------+------------+
| 2 | 83.500000 |
| 3 | 73.583333 |
+---------+------------+
2行セット

サブクエリの説明

列サブクエリは、複数行演算子 (in (not in)、any/some、all) とともに使用する必要があります。 distinctive キーワードを使用して重複を削除すると、実行効率が向上します。

サブクエリの例 + in: クラス3以外のすべての生徒

mysql> select * from students a where a.classid in (select distinctive b.classid from classes b where b.classid <3);
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 1 | ブランド | 97.5 | 1 |
| 2 | ヘレン | 96.5 | 1 |
| 3 | リン | 96 | 1 |
| 4 | ソル | 97 | 1 |
| 5 | ウェン | 100 | 1 |
| 6 | ディニー | 92.7 | 1 |
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 9 | b3 | 83 | 2 |
| 10 | b4 | 84 | 2 |
| 11 | b5 | 85 | 2 |
| 12 | b6 | 86 | 2 |
+-----------+-------------+--------+---------+
12行セット

サブクエリの例 + any: クラス3にいないすべての生徒

mysql> select * from students a where a.classid = any (select distinctive b.classid from classes b where b.classid <3);
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 1 | ブランド | 97.5 | 1 |
| 2 | ヘレン | 96.5 | 1 |
| 3 | リン | 96 | 1 |
| 4 | ソル | 97 | 1 |
| 5 | ウェン | 100 | 1 |
| 6 | ディニー | 92.7 | 1 |
| 7 | b1 | 81 | 2 |
| 8 | b2 | 82 | 2 |
| 9 | b3 | 83 | 2 |
| 10 | b4 | 84 | 2 |
| 11 | b5 | 85 | 2 |
| 12 | b6 | 86 | 2 |
+-----------+-------------+--------+---------+
12行セット

サブクエリ + all: not in と同等

mysql> select * from students a where a.classid <> all (select distinctive b.classid from classes b where b.classid <3);
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 13 | c1 | 71 | 3 |
| 14 | c2 | 72.5 | 3 |
| 15 | c3 | 73 | 3 |
| 16 | c4 | 74 | 3 |
| 17 | c5 | 75 | 3 |
| 18 | c6 | 76 | 3 |
+-----------+-------------+--------+---------+
6行セット

行サブクエリの説明

学生番号が最も小さいが成績が最も良い学生を照会します。

mysql> select * from students a where (a.studentid, a.score) in (select max(studentid),min(score) from students);
+-----------+-------------+--------+---------+
| 学生ID | 学生名 | スコア | クラスID |
+-----------+-------------+--------+---------+
| 19 | ララ | 51 | 0 |
+-----------+-------------+--------+---------+
セット内の1行

サブクエリが存在する

相関サブクエリとも呼ばれ、外側のレイヤー (複数の行と列をサポート) のクエリ結果を取得して内側のレイヤーに持ち込み、内側のレイヤーが確立されているかどうかを確認します。簡単に言うと、外側のレイヤー (つまり、前のステートメント) は、後者が true を返す場合にのみ実行され、それ以外の場合は実行されません。

1. Exists クエリの結果: 1 または 0。1 は true、0 は false。Exists クエリの結果は、サブクエリの結果セットに値があるかどうかを判断するために使用されます。

2. exists サブクエリは通常 in に置き換えることができるため、exists が使用されることはほとんどありません。

3. 以前のクエリ方法とは異なり、最初にメインクエリが実行され、次にサブクエリの結果がメインクエリの結果に応じてフィルタリングするために使用されます。サブクエリにはメインクエリで使用されるフィールドが含まれているため、相関サブクエリとも呼ばれます。

例: 全生徒のクラス名を照会する

mysql> クラス名を classes a から選択します。存在する場合は、(学生 b から 1 を選択します。b.classid = a.classid);

+-----------+
|クラス名|
+-----------+
| クラス 1、グレード 9|
| クラス 2、グレード 9|
| クラス 3、グレード 9|
+-----------+
3行セット

代わりに in を使用してください(見た目がシンプルになります)。

mysql> クラス名を classes a から選択します。ここで、a.classid は students から classid を選択します。

+-----------+
|クラス名|
+-----------+
| クラス 1、グレード 9|
| クラス 2、グレード 9|
| クラス 3、9 年生|
+-----------+
3行セット

複合クエリ

ほとんどの SQL クエリは、1 つ以上のテーブルからデータを返す単一の SELECT ステートメントで構成されます。 MySQL では、複数のクエリ (複数の SELECT ステートメント) を実行し、結果を単一のクエリ結果セットとして返すこともできます。これらの結合クエリは、多くの場合、ユニオン クエリまたは複合クエリと呼ばれます。

1 つのテーブルに対する複数の戻り値

異なるクエリの結果を組み合わせる

 条件1でtnameからcname1、cname2を選択
 連合
 条件2でtnameからcname1、cname2を選択

複数のテーブルが同じ構造を返す

同じ数量構造を持つフィールドを結合する

 tname1 から t1_cname1,t1_cname2 を選択する (条件付き)
 連合
 条件付きでtname2からt2_cname1、t_2cname2を選択

ここでは詳細には触れませんが、これについては後で特別な章で説明します。

要約する

クエリの戻り値の型とステートメント内のサブクエリの位置という 2 つの側面から学習できます。

in、any、some、allの使用に注意してください

比較、クエリ、カウントのいずれの場合でも、フィールド内の null 値は常に誤解を招きます。テーブルを作成するときにフィールドを空にしないか、デフォルト値を指定することをお勧めします。

上記はMySQLサブクエリとグループクエリの詳細です。MySQLクエリの詳細については、123WORDPRESS.COMの他の関連記事をご覧ください。

以下もご興味があるかもしれません:
  • Mysql マルチレイヤーサブクエリのサンプルコード (お気に入りの場合)
  • MySQLサブクエリの原理の詳細な分析
  • Mysql の複数行サブクエリと null 値の問題を解決する
  • MySQL チュートリアル: サブクエリの例の詳細な説明
  • mysql サブクエリと結合テーブルの詳細
  • MySQL の結合クエリとサブクエリの問題
  • MySQL でのサブクエリの基本的な使用法
  • MySQLサブクエリの詳細な例
  • MySQL サブクエリの使用に関する詳細な分析

<<:  Nginx + consul + upsync を使用して動的負荷分散を実現する方法の詳細な説明

>>:  vue2 vue3 での Echarts の詳細な使用方法

推薦する

MySQLソースコマンドの使い方の紹介

目次ネット上の質問から生まれた思考MySQL ソースコマンドネット上の質問から生まれた思考今日仕事中...

HTML における li タグの水平配置の例

ほとんどのナビゲーション バーは、下の図に示すように水平に配置されていますが、これはどのように実現さ...

MySQL 5.7.20 Green Edition のインストールの詳細なグラフィックチュートリアル

まず、MySQL とは何かを理解しましょう。 MySQL は、スウェーデンの会社 MySQL AB ...

Javascript を使用して、スライドバー効果のあるスライドナビゲーション プラグインを開発します。

目次1. はじめに2. 使用方法3. 開発プロセス1. モデル例2. イベントとアニメーション4. ...

MySQLはIDに適切なデータ型を選択します

目次分散IDソリューションの概要データベース自動増分IDデータベースマルチマスターモード数値セグメン...

フロントエンドでよく使われるjs関数メソッド

目次1. メール2. 携帯電話番号3. 電話番号4. URLアドレスですか? 5. 文字列ですか? ...

CSSは固定比率のブロックレベルコンテナを簡単に実装できる

H5 レイアウトを設計する場合、通常はバナーに遭遇することになります。例えば、2:1 で表示したい場...

MySQLデータ移行方法とツールの分析

この記事は主にMySQLデータ移行方法とツールの分析を紹介します。サンプルコードを通じて詳細に紹介さ...

MySQL 権限制御の詳細分析

目次1. グローバルレベル2. データベースレベル3. 表面レベル4. 列レベルの権限5. サブルー...

Vue での bimface の使用に関する詳細

目次1. Vue スキャフォールディングをインストールする2. プロジェクトを作成する3.1 プロジ...

MySQL 分離レベルの詳細な説明と例

目次MySQL の 4 つの分離レベルデータ テーブルを作成します。分離レベルの設定物事の分離レベル...

MySQL SHOW STATUSステートメントの使用

MySQL のパフォーマンス調整とサービス ステータスの監視を行うには、MySQL の現在の実行状態...

Mysql 5.7.19 無料インストール バージョンで遭遇した落とし穴 (コレクション)

1. 公式ウェブサイトから 64 ビットの zip ファイルをダウンロードします。 2. インスト...

カスタム変数を使用した MySQL クエリの最適化

目次並べ替えクエリの最適化変更されたばかりのデータ行を繰り返し取得しないようにする遅延ロードされた結...

Typescriptを使用してWeChatミニプログラムでプロジェクトを作成する方法

プロジェクトを作成するWeChat開発者ツールでプロジェクトを作成し、言語でTypeScriptを選...