mysql - 結合 - sql 複数テーブル 条件




SQLクエリは複数のテーブルからデータを返します (4)

私は以下を知りたいと思います:

  • 私のデータベースの複数のテーブルからデータを取得する方法は?
  • これを行うにはどのような方法がありますか?
  • 結合と組合とは何ですか?そして、彼らはどのようにお互いに異なっていますか?
  • いつ他のものと比較してそれぞれを使うべきですか?

私は私の(例えば - PHPの)アプリケーションでこれを使用する予定ですが、データベースに対して複数のクエリを実行したくない場合、単一のクエリで複数のテーブルからデータを取得するにはどのようなオプションが必要ですか?

注:私はこれを書いています。なぜなら、PHPキューで絶えず遭遇する多数の質問に関するよく書かれたガイドにリンクすることができるようになるためです。

答えは以下をカバーしています:

  1. パート1 - 結合と結合
  2. パート2 - サブクエリ
  3. パート3 - トリックと効率的なコード
  4. 第4部 - From句のサブクエリ
  5. パート5 - John's Tricksの混合バッグ

パート1 - 結合と結合

この回答には、

  1. パート1
    • 内部結合を使用して2つ以上のテーブルを結合する(詳細については、 ウィキペディアのエントリを参照してください)
    • ユニオンクエリの使用方法
    • 左右外側の結合(このの答えは結合の種類を記述するのに優れています)
    • (データベースをサポートしていない場合、それらを再現する方法) - これはSQL Serverの機能です( 情報を参照 )。そして、 私がこの全部を最初に書い理由の一部です。
  2. パート2
    • サブクエリ - サブスクリプション、サブスクリプション、サブスクリプション
    • デカルト結合はああ - ああ、不幸!

データベース内の複数のテーブルからデータを取得するには、さまざまな方法があります。 この回答では、ANSI-92結合構文を使用します。 これは、以前のANSI-89構文を使用する多くの他のチュートリアルとは異なる場合があります(89に慣れていれば、直感的には見えないかもしれませんが、試してみてください)。クエリがいつより複雑になるかを理解することができます。 なぜそれを使用するのですか? パフォーマンスの向上はありますか? 短い答えは「いいえ」ですが、慣れたらすぐに読むことできます。 この構文を使用すると、他の人々が作成したクエリを読みやすくなります。

私はまた、利用可能な車を追跡するためのデータベースを持つ小さなcaryardの概念を使用するつもりです。 所有者はITコンピュータの人としてあなたを雇い、帽子のドロップで彼が求めているデータを彼にドロップさせることを期待しています。

私はファイナルテーブルで使用されるいくつかのルックアップテーブルを作成しました。 これは私たちに合理的なモデルを提供します。 まず、以下の構造を持つサンプルデータベースに対してクエリを実行します。 私は出発時に起こるよくある間違いを考え、それらに間違っていることを説明します。もちろん、それらを修正する方法を示しています。

最初の表は単なるカラーリストで、私たちは車の庭にどのような色があるのか​​を知っています。

mysql> create table colors(id int(3) not null auto_increment primary key, 
    -> color varchar(15), paint varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from colors;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| color | varchar(15) | YES  |     | NULL    |                |
| paint | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

mysql> insert into colors (color, paint) values ('Red', 'Metallic'), 
    -> ('Green', 'Gloss'), ('Blue', 'Metallic'), 
    -> ('White' 'Gloss'), ('Black' 'Gloss');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from colors;
+----+-------+----------+
| id | color | paint    |
+----+-------+----------+
|  1 | Red   | Metallic |
|  2 | Green | Gloss    |
|  3 | Blue  | Metallic |
|  4 | White | Gloss    |
|  5 | Black | Gloss    |
+----+-------+----------+
5 rows in set (0.00 sec)

ブランド表は、caryardが販売する可能性のある車の異なるブランドを識別します。

mysql> create table brands (id int(3) not null auto_increment primary key, 
    -> brand varchar(15));
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from brands;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| brand | varchar(15) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> insert into brands (brand) values ('Ford'), ('Toyota'), 
    -> ('Nissan'), ('Smart'), ('BMW');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from brands;
+----+--------+
| id | brand  |
+----+--------+
|  1 | Ford   |
|  2 | Toyota |
|  3 | Nissan |
|  4 | Smart  |
|  5 | BMW    |
+----+--------+
5 rows in set (0.00 sec)

モデルテーブルは異なる種類の車をカバーしますが、実際の車種ではなく異なる車種を使用する方が簡単です。

mysql> create table models (id int(3) not null auto_increment primary key, 
    -> model varchar(15));
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from models;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| model | varchar(15) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> insert into models (model) values ('Sports'), ('Sedan'), ('4WD'), ('Luxury');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from models;
+----+--------+
| id | model  |
+----+--------+
|  1 | Sports |
|  2 | Sedan  |
|  3 | 4WD    |
|  4 | Luxury |
+----+--------+
4 rows in set (0.00 sec)

そして最後に、これらすべての他のテーブルを結びつけるために、すべてを結びつけるテーブル。 IDフィールドは実際には車を識別するために使用される固有のロット番号です。

mysql> create table cars (id int(3) not null auto_increment primary key, 
    -> color int(3), brand int(3), model int(3));
Query OK, 0 rows affected (0.01 sec)

mysql> show columns from cars;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| id    | int(3) | NO   | PRI | NULL    | auto_increment |
| color | int(3) | YES  |     | NULL    |                |
| brand | int(3) | YES  |     | NULL    |                |
| model | int(3) | YES  |     | NULL    |                |
+-------+--------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into cars (color, brand, model) values (1,2,1), (3,1,2), (5,3,1), 
    -> (4,4,2), (2,2,3), (3,5,4), (4,1,3), (2,2,1), (5,2,3), (4,5,1);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from cars;
+----+-------+-------+-------+
| id | color | brand | model |
+----+-------+-------+-------+
|  1 |     1 |     2 |     1 |
|  2 |     3 |     1 |     2 |
|  3 |     5 |     3 |     1 |
|  4 |     4 |     4 |     2 |
|  5 |     2 |     2 |     3 |
|  6 |     3 |     5 |     4 |
|  7 |     4 |     1 |     3 |
|  8 |     2 |     2 |     1 |
|  9 |     5 |     2 |     3 |
| 10 |     4 |     5 |     1 |
+----+-------+-------+-------+
10 rows in set (0.00 sec)

これにより、さまざまなタイプの結合の下の例をカバーし、価値あるものにするための十分なデータを提供するのに十分なデータ(私が望む)が得られます。

それで、ボスは彼が持っているすべてのスポーツカーのIDを知りたいと思ってます。

これは単純な2テーブル結合です。 モデルとテーブルを利用可能な在庫で識別するテーブルがあります。 ご覧のように、 carsテーブルのmodel列のデータは、 carsテーブルのmodels列に関連してmodelsます。 ここで、モデルテーブルはSports IDが1であるので、結合を書くことができます。

select
    ID,
    model
from
    cars
        join models
            on model=ID

だからこのクエリはうまく見える? 2つのテーブルが識別され、必要な情報が含まれており、結合する列を正しく識別する結合を使用しています。

ERROR 1052 (23000): Column 'ID' in field list is ambiguous

ああああ! 最初のクエリのエラー! はい、それは梅です。 ご存じのように、クエリは実際には正しい列を持っていますが、それらのうちのいくつかは両方のテーブルに存在するため、データベースは実際の列と意味を混同します。 これを解決するには2つの解決策があります。 最初は素敵でシンプルなtableName.columnNametableName.columnNameを使ってデータベースに正確に何を意味するかを以下のように伝えることができます:

select
    cars.ID,
    models.model
from
    cars
        join models
            on cars.model=models.ID

+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  3 | Sports |
|  8 | Sports |
| 10 | Sports |
|  2 | Sedan  |
|  4 | Sedan  |
|  5 | 4WD    |
|  7 | 4WD    |
|  9 | 4WD    |
|  6 | Luxury |
+----+--------+
10 rows in set (0.00 sec)

もう1つはおそらくより頻繁に使用され、テーブルエイリアシングと呼ばれます。 この例の表は素敵で短いシンプルな名前を持っていますが、 KPI_DAILY_SALES_BY_DEPARTMENTようなものを入力するとすぐに古いものになるので、簡単な方法は次のように表にニックネームを付けることです:

select
    a.ID,
    b.model
from
    cars a
        join models b
            on a.model=b.ID

今、要求に戻る。 あなたが見ることができるように、私たちは必要な情報を持っていますが、尋ねられなかった情報も持っているので、尋ねられたようにSportsカーを得るためにwhere節を文に含める必要があります。 テーブル名を何度も使用するのではなく、テーブルエイリアスメソッドを好むので、私はこの点以降これに留意します。

明らかに、where句をクエリに追加する必要があります。 ID=1またはmodel='Sports'いずれかでスポーツカーを識別できます。 IDが索引付けされ、プライマリ・キー(そしてそれが発生すると、入力が少なくなるため)、これをクエリで使用できます。

select
    a.ID,
    b.model
from
    cars a
        join models b
            on a.model=b.ID
where
    b.ID=1

+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  3 | Sports |
|  8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)

ビンゴ! 上司は満足しています。 もちろん、上司であり、彼が求めたことに決して満足していないと、彼は情報を見て、色も欲しいと言っています。

さて、私たちはクエリの良い部分をすでに書いていますが、色である第3のテーブルを使用する必要があります。 さて、私たちの主な情報テーブルcarsは車の色IDを保存し、これは色のID列に戻ってリンクします。 したがって、オリジナルと同様の方法で、3番目のテーブルに参加できます。

select
    a.ID,
    b.model
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
where
    b.ID=1

+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  3 | Sports |
|  8 | Sports |
| 10 | Sports |
+----+--------+
4 rows in set (0.00 sec)

ちょっと、テーブルが正しく結合され、関連する列がリンクされていたにもかかわらず、リンクしたばかりの新しいテーブルから実際の情報を引き出すのを忘れてしまった。

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
where
    b.ID=1

+----+--------+-------+
| ID | model  | color |
+----+--------+-------+
|  1 | Sports | Red   |
|  8 | Sports | Green |
| 10 | Sports | White |
|  3 | Sports | Black |
+----+--------+-------+
4 rows in set (0.00 sec)

そうです、それは私たちの背中の上司です。 今、もう少し詳しく説明します。 ご覧のように、ステートメントのfrom句はメインテーブルをリンクしています(私はしばしば、ルックアップやディメンションテーブルではなく情報を含むテーブルを使用します)。このクエリに戻って数ヶ月で読むことができますので、わかりやすく、わかりやすいクエリを書くことをお勧めします - 直感的にレイアウトし、すてきなインデントを使ってすべてが明確であるようにしてくださいあなたが他者を教えるために行くなら、特にあなたがそれらをトラブルシューティングするならば、彼らの質問にこれらの特徴を植えつけてみてください。

このようにして、より多くのテーブルをつなぎ合わせることは完全に可能です。

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1

私はjoin文の中で複数のカラムを結合したいかもしれないテーブルをインクルードするのを忘れていましたjoin 、ここに例があります。 models表にブランド固有のモデルがあり、 IDフィールドのbrands表にリンクされたbrandという列もある場合は、次のようにして行うことができます。

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
            and b.brand=d.ID
where
    b.ID=1

上記のクエリは、結合されたテーブルをメインcarsテーブルにリンクするだけでなく、既に結合されたテーブル間の結合も指定します。 これが行われなかった場合、結果はデカルト結合と呼ばれます。 カルテシアン・ジョインは、情報がデータベースに結果の制限方法を知らせないために行が戻されるため、照会は条件に合致するすべての行を戻します。

したがって、デカルト結合の例を示すために、次のクエリを実行できます。

select
    a.ID,
    b.model
from
    cars a
        join models b

+----+--------+
| ID | model  |
+----+--------+
|  1 | Sports |
|  1 | Sedan  |
|  1 | 4WD    |
|  1 | Luxury |
|  2 | Sports |
|  2 | Sedan  |
|  2 | 4WD    |
|  2 | Luxury |
|  3 | Sports |
|  3 | Sedan  |
|  3 | 4WD    |
|  3 | Luxury |
|  4 | Sports |
|  4 | Sedan  |
|  4 | 4WD    |
|  4 | Luxury |
|  5 | Sports |
|  5 | Sedan  |
|  5 | 4WD    |
|  5 | Luxury |
|  6 | Sports |
|  6 | Sedan  |
|  6 | 4WD    |
|  6 | Luxury |
|  7 | Sports |
|  7 | Sedan  |
|  7 | 4WD    |
|  7 | Luxury |
|  8 | Sports |
|  8 | Sedan  |
|  8 | 4WD    |
|  8 | Luxury |
|  9 | Sports |
|  9 | Sedan  |
|  9 | 4WD    |
|  9 | Luxury |
| 10 | Sports |
| 10 | Sedan  |
| 10 | 4WD    |
| 10 | Luxury |
+----+--------+
40 rows in set (0.00 sec)

良い神、それは醜いです。 しかし、データベースに関する限り、それはまさに要求されたものです。 クエリでは、 carsからのIDmodelからのmodelsを求めました。 ただし、テーブルの結合方法は指定されていないため、データベースは最初のテーブルのすべての行と2番目のテーブルのすべての行を照合しました。

さて、上司が戻ってきたので、彼はさらに情報を求めています。 私は同じリストが欲しいだけでなく、4WDも含めています。

しかしこれは、これを達成するための2つの異なる方法を見てすばらしい言い訳です。 where節に次のような条件を追加することができます。

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1
    or b.ID=3

上記は完全にうまくいくはずですが、それを別の方法で見てみましょう。これは、 unionクエリがどのように機能するかを示す大きな理由です。

我々は、次のことがすべてのスポーツカーを返すことを知っている:

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1

そして、次のようにすると4WDがすべて返されます:

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=3

したがって、それらの間にUNION ALL句を追加すると、2番目のクエリの結果が最初のクエリの結果に追加されます。

select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=1
union all
select
    a.ID,
    b.model,
    c.color
from
    cars a
        join models b
            on a.model=b.ID
        join colors c
            on a.color=c.ID
        join brands d
            on a.brand=d.ID
where
    b.ID=3

+----+--------+-------+
| ID | model  | color |
+----+--------+-------+
|  1 | Sports | Red   |
|  8 | Sports | Green |
| 10 | Sports | White |
|  3 | Sports | Black |
|  5 | 4WD    | Green |
|  7 | 4WD    | White |
|  9 | 4WD    | Black |
+----+--------+-------+
7 rows in set (0.00 sec)

ご覧のとおり、最初のクエリの結果が最初に返され、次に2番目のクエリの結果が返されます。

この例では、最初のクエリを単純に使用する方がはるかに簡単ですが、 unionクエリは特定のケースでは優れています。 これらは、簡単に一緒に結合されない表の表から特定の結果を戻すためのすばらしい方法です。 しかし、いくつかのルールがあります。

  • 最初のクエリの列型は、以下のすべてのクエリの列型と一致する必要があります。
  • 最初のクエリの列の名前は、結果セット全体を識別するために使用されます。
  • 各クエリの列数は同じでなければなりません。

さて、あなたは、 unionunion allを使うことの違いが何であるか疑問に思い union allunionクエリは重複を削除しますが、 unionは削除しません。 これは、 union上でunion allを使用するとパフォーマンスが低下することを意味しunion allが、結果には価値があるかもしれません - この場合でもこの種のことは推測しません。

このノートでは、ここでいくつかの追加の注意点に注目する価値があります。

  • 結果を注文したい場合は注文を使用できますが、もう別名を使用することはできません。 上のクエリでは、 order by a.ID追加するとエラーが発生します。同じエイリアスが両方のクエリで使用されていても、結果が関係する限り、列はa.IDではなくIDと呼ばれID
  • 私たちはorder byステートメントごとに1つだけを持つことができ、それは最後のステートメントでなければなりません。

次の例では、テーブルにいくつかの行を追加しています。

私はHoldenをブランドテーブルに追加しました。 color値が12 cars行を追加しました。色のテーブルには参照がありません。

さて、上司がまた戻って、リクエストを吠える - *私たちが運ぶ各ブランドの数とその中の車の数がほしい! - 典型的な、我々は議論の興味深いセクションに行き、上司はより多くの仕事を望んでいる。

そう、まず最初にするべきことは、可能なブランドの完全なリストを得ることです。

select
    a.brand
from
    brands a

+--------+
| brand  |
+--------+
| Ford   |
| Toyota |
| Nissan |
| Smart  |
| BMW    |
| Holden |
+--------+
6 rows in set (0.00 sec)

さて、これを車のテーブルに加えると、次の結果が得られます:

select
    a.brand
from
    brands a
        join cars b
            on a.ID=b.brand
group by
    a.brand

+--------+
| brand  |
+--------+
| BMW    |
| Ford   |
| Nissan |
| Smart  |
| Toyota |
+--------+
5 rows in set (0.00 sec)

それはもちろん問題です - 私が追加した素敵なHoldenブランドの言及は見ていません。

これは、結合が両方のテーブルで一致する行を検索するためです。 Holden型の車にはデータがないため、返されません。 ここではouter結合を使用できます。 これは、他のテーブルで一致しているかどうかにかかわらず、1つのテーブルからすべての結果を返します

select
    a.brand
from
    brands a
        left outer join cars b
            on a.ID=b.brand
group by
    a.brand

+--------+
| brand  |
+--------+
| BMW    |
| Ford   |
| Holden |
| Nissan |
| Smart  |
| Toyota |
+--------+
6 rows in set (0.00 sec)

これで、素敵な集計関数を追加してカウントを取得し、しばらく私たちの背中から上司を得ることができます。

select
    a.brand,
    count(b.id) as countOfBrand
from
    brands a
        left outer join cars b
            on a.ID=b.brand
group by
    a.brand

+--------+--------------+
| brand  | countOfBrand |
+--------+--------------+
| BMW    |            2 |
| Ford   |            2 |
| Holden |            0 |
| Nissan |            1 |
| Smart  |            1 |
| Toyota |            5 |
+--------+--------------+
6 rows in set (0.00 sec)

そしてそれで、ボスのスカンクを離れてください。

今、これについてもう少し詳しく説明するために、外部結合はleftまたはright型にすることができます。 左または右は、どのテーブルが完全に含まれているかを定義します。 left outer joinには、テーブルのすべての行が左に表示されます(あなたは推測しました)。右外部right outer joinは、右のテーブルのすべての結果を結果right outer joinもたらします。

データベースによっては、 両方のテーブルから結果(一致しているかどうかにかかわらず)を戻すfull outer joinが可能ですが、これはすべてのデータベースでサポートされているわけではありません。

さて、この時点では、クエリの結合タイプをマージできるかどうか疑問に思っています。答えは「はい」です。

select
    b.brand,
    c.color,
    count(a.id) as countOfBrand
from
    cars a
        right outer join brands b
            on b.ID=a.brand
        join colors c
            on a.color=c.ID
group by
    a.brand,
    c.color

+--------+-------+--------------+
| brand  | color | countOfBrand |
+--------+-------+--------------+
| Ford   | Blue  |            1 |
| Ford   | White |            1 |
| Toyota | Black |            1 |
| Toyota | Green |            2 |
| Toyota | Red   |            1 |
| Nissan | Black |            1 |
| Smart  | White |            1 |
| BMW    | Blue  |            1 |
| BMW    | White |            1 |
+--------+-------+--------------+
9 rows in set (0.00 sec)

だから、なぜそれは期待された結果ではないのですか? 車からブランドへの外部結合を選択したにもかかわらず、色への結合では指定されていないため、特定の結合では両方のテーブルに一致する結果のみが戻されるからです。

期待される結果を得るために必要なクエリは次のとおりです。

select
    a.brand,
    c.color,
    count(b.id) as countOfBrand
from
    brands a
        left outer join cars b
            on a.ID=b.brand
        left outer join colors c
            on b.color=c.ID
group by
    a.brand,
    c.color

+--------+-------+--------------+
| brand  | color | countOfBrand |
+--------+-------+--------------+
| BMW    | Blue  |            1 |
| BMW    | White |            1 |
| Ford   | Blue  |            1 |
| Ford   | White |            1 |
| Holden | NULL  |            0 |
| Nissan | Black |            1 |
| Smart  | White |            1 |
| Toyota | NULL  |            1 |
| Toyota | Black |            1 |
| Toyota | Green |            2 |
| Toyota | Red   |            1 |
+--------+-------+--------------+
11 rows in set (0.00 sec)

ご覧のとおり、クエリには2つの外部結合があり、結果は期待どおりに処理されます。

今、あなたが求める他のタイプの結合についてはどうですか? 交差点はどうですか?

すべてのデータベースがintersectionサポートしているわけではありませんが、ほとんどすべてのデータベースでは、結合(または構造化されたwhere文)を使用して交差を作成できます。

交差点は、上記のように結合体に幾分類似した結合の型ですが、違いは、結合体によって結合されたさまざまな個々の問い合わせの間で同じ(そして私は同じことを意味する)データの行だけを返すことです。 すべての点で同一の行のみが返されます。

簡単な例は次のようになります。

select
    *
from
    colors
where
    ID>2
intersect
select
    *
from
    colors
where
    id<4

通常のunionクエリでは、テーブルのすべての行( ID>2以上のID>2返す最初のクエリとID<4を持つもの)がすべて返されますが、交差クエリはid=3一致する行のみを返しますid=3

ここでデータベースがintersect問合せをサポートしていない場合は、上記の問合せを次の問合せで簡単に実行できます。

select
    a.ID,
    a.color,
    a.paint
from
    colors a
        join colors b
            on a.ID=b.ID
where
    a.ID>2
    and b.ID<4

+----+-------+----------+
| ID | color | paint    |
+----+-------+----------+
|  3 | Blue  | Metallic |
+----+-------+----------+
1 row in set (0.00 sec)

本来、交差問合せをサポートしていないデータベースを使用して2つの異なる表の交差を実行する場合は、表の各列に結合を作成する必要があります。


パート2 - サブクエリ

さて、上司が再び爆発しました - 私は、ブランドと私たちが持っているブランドの数の合計ですべての車のリストが欲しいです!

これは、SQLのおいしさの中で次のトリックを使用する絶好の機会です。サブクエリです。 この用語に精通していない場合、サブクエリは別のクエリで実行されるクエリです。 それらを使用するにはさまざまな方法があります。

私たちの要求に応えるために、最初に単純なクエリを入れて、それぞれの車とブランドをリストアップします:

select
    a.ID,
    b.brand
from
    cars a
        join brands b
            on a.brand=b.ID

さて、単にブランド別にソートされた車の数を取得したいのであれば、もちろんこれを書くことができます:

select
    b.brand,
    count(a.ID) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID
group by
    b.brand

+--------+-----------+
| brand  | countCars |
+--------+-----------+
| BMW    |         2 |
| Ford   |         2 |
| Nissan |         1 |
| Smart  |         1 |
| Toyota |         5 |
+--------+-----------+

だから、カウント関数を元のクエリの権利に単純に追加できるはずです。

select
    a.ID,
    b.brand,
    count(a.ID) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID
group by
    a.ID,
    b.brand

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  1 | Toyota |         1 |
|  2 | Ford   |         1 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  5 | Toyota |         1 |
|  6 | BMW    |         1 |
|  7 | Ford   |         1 |
|  8 | Toyota |         1 |
|  9 | Toyota |         1 |
| 10 | BMW    |         1 |
| 11 | Toyota |         1 |
+----+--------+-----------+
11 rows in set (0.00 sec)

悲しいことに、私たちはそれをすることはできません。 その理由は、車のID(列a.ID)を追加するときには、それをグループに追加する必要があるからです。つまり、カウント機能が働くと、IDごとに一致するIDは1つだけです。

しかし、ここではサブクエリを使用できます。実際には、これと同じ結果を返す2つの完全に異なるタイプのサブクエリを実行できます。 最初は、 select句にサブクエリを置くだけです。 これは、データの行を取得するたびにサブクエリが実行され、データの列が取得され、データの行にポップされることを意味します。

select
    a.ID,
    b.brand,
    (
    select
        count(c.ID)
    from
        cars c
    where
        a.brand=c.brand
    ) as countCars
from
    cars a
        join brands b
            on a.brand=b.ID

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  2 | Ford   |         2 |
|  7 | Ford   |         2 |
|  1 | Toyota |         5 |
|  5 | Toyota |         5 |
|  8 | Toyota |         5 |
|  9 | Toyota |         5 |
| 11 | Toyota |         5 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  6 | BMW    |         2 |
| 10 | BMW    |         2 |
+----+--------+-----------+
11 rows in set (0.00 sec)

そしてバム!、これは私たちをするだろう。 あなたが気づいた場合、このサブクエリは、私たちが返すデータのすべての行ごとに実行する必要があります。 この小さな例でも、5つの異なるブランドの車がありますが、サブクエリは11回実行され、11行のデータが返されます。 したがって、この場合、コードを書くのに最も効率的な方法のようには見えません。

別のアプローチでは、サブクエリを実行して、それがテーブルであるとふります。

select
    a.ID,
    b.brand,
    d.countCars
from
    cars a
        join brands b
            on a.brand=b.ID
        join
            (
            select
                c.brand,
                count(c.ID) as countCars
            from
                cars c
            group by
                c.brand
            ) d
            on a.brand=d.brand

+----+--------+-----------+
| ID | brand  | countCars |
+----+--------+-----------+
|  1 | Toyota |         5 |
|  2 | Ford   |         2 |
|  3 | Nissan |         1 |
|  4 | Smart  |         1 |
|  5 | Toyota |         5 |
|  6 | BMW    |         2 |
|  7 | Ford   |         2 |
|  8 | Toyota |         5 |
|  9 | Toyota |         5 |
| 10 | BMW    |         2 |
| 11 | Toyota |         5 |
+----+--------+-----------+
11 rows in set (0.00 sec)

さて、同じ結果が得られました(わずかに異なりますが、データベースは今回選択した最初の列の結果を返すことを望んでいたようです)が、同じ右の数値です。

では、これら2つの違いは何ですか?それぞれのタイプのサブクエリをいつ使用する必要がありますか? まず、2番目のクエリの仕組みを理解できるようにします。 クエリのfrom句で2つのテーブルを選択した後、クエリを書き、実際にはテーブルではなくデータベースに完全に満足していることをデータベースに伝えました。 この方法の使用にはいくつかの利点があります (いくつかの制限があります)。 まず、このサブクエリが一度だけ実行されたことです。 データベースに大量のデータが含まれていると、最初の方法よりも大幅に改善される可能性があります。 しかし、これをテーブルとして使用しているので、実際にデータの行に再結合できるように、追加の行のデータを取り込む必要があります。 また、上記のクエリのように単純な結合を使用する場合は、 十分なデータ行があることを確認する必要があります。 あなたが思い出すと、結合は、結合の両側で一致するデータを持つ行だけを引き戻します。 注意しないと、このサブクエリに一致する行がない場合、有効なデータが車のテーブルから返されない可能性があります。

さて、最初のサブクエリを振り返ってみると、いくつかの制限もあります。 データを1行に戻すので、1行のデータだけを取り戻すことができます。 クエリのselect句で使用されるサブクエリは、 sumcountmaxなどの集約関数のみを使用することがよくあります。 彼らはする必要はありません 、それはしばしば書かれている方法です。

だから、私たちに移る前に、どこでサブクエリを使うことができるのかを見てみましょう。私たちはwhere節でそれを使うことができます- 今、この例は私たちのデータベースのように少し工夫されていますが、以下のデータを取得するより良い方法がありますが、それは一例に過ぎません。

select
    ID,
    brand
from
    brands
where
    brand like '%o%'

+----+--------+
| ID | brand  |
+----+--------+
|  1 | Ford   |
|  2 | Toyota |
|  6 | Holden |
+----+--------+
3 rows in set (0.00 sec)

これにより、ブランドIDとブランド名のリストが返されます(2番目の列は、ブランドを示すためにのみ追加されます)o

これで、where句でこのクエリの結果を使用できます。

select
    a.ID,
    b.brand
from
    cars a
        join brands b
            on a.brand=b.ID
where
    a.brand in
        (
        select
            ID
        from
            brands
        where
            brand like '%o%'
        )

+----+--------+
| ID | brand  |
+----+--------+
|  2 | Ford   |
|  7 | Ford   |
|  1 | Toyota |
|  5 | Toyota |
|  8 | Toyota |
|  9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

ご覧のように、サブクエリが3つのブランドIDを返していたにもかかわらず、私たちのcarsテーブルには2つのブランドIDのエントリしかありませんでした。

この場合、詳細については、次のコードを記述したかのようにサブクエリが機能しています。

select
    a.ID,
    b.brand
from
    cars a
        join brands b
            on a.brand=b.ID
where
    a.brand in (1,2,6)

+----+--------+
| ID | brand  |
+----+--------+
|  1 | Toyota |
|  2 | Ford   |
|  5 | Toyota |
|  7 | Ford   |
|  8 | Toyota |
|  9 | Toyota |
| 11 | Toyota |
+----+--------+
7 rows in set (0.00 sec)

ここでも、サブクエリと手動入力がデータベースから戻るときに行の順序がどのように変更されたかを確認できます。

サブクエリについて議論しているうちに、サブクエリを使って他に何ができるかを見てみましょう。

  • 別のサブクエリ内にサブクエリを配置することができます。あなたのデータベースに依存する制限がありますが、狂気のようなプログラマの再帰的な機能が不足しているので、ほとんどの人は決してその限界にぶつからないでしょう。
  • 1つのクエリに複数のサブクエリを配置することも、いくつかのサブクエリを配置することも、いくつかのサブクエリをselectいくつかのfrom節にwhere配置することもできます。実行する。

効率的なコードを書く必要がある場合は、クエリをさまざまな方法で記述し、結果を得るのに最適なクエリである(タイミングをとるか、またはExplainプランを使用するかによって)参照することが有益です。最初に働く方法が必ずしも最良の方法であるとは限りません。


この記事は非常に興味深いものですが、私はクエリ作成に関する知識を共有したいと思います。 このFluffehありがとう。 これを読んで私が間違っていると感じるかもしれない他の人は私の答えを編集して批判するのは101%です。 ( 正直なところ、私は間違いを訂正して感謝しています。

私はMySQLタグのよくある質問のいくつかを投稿しMySQL

トリックNo.1( 複数の条件に一致する行

このスキーマを考えると

CREATE TABLE MovieList
(
    ID INT,
    MovieName VARCHAR(25),
    CONSTRAINT ml_pk PRIMARY KEY (ID),
    CONSTRAINT ml_uq UNIQUE (MovieName)
);

INSERT INTO MovieList VALUES (1, 'American Pie');
INSERT INTO MovieList VALUES (2, 'The Notebook');
INSERT INTO MovieList VALUES (3, 'Discovery Channel: Africa');
INSERT INTO MovieList VALUES (4, 'Mr. Bean');
INSERT INTO MovieList VALUES (5, 'Expendables 2');

CREATE TABLE CategoryList
(
    MovieID INT,
    CategoryName VARCHAR(25),
    CONSTRAINT cl_uq UNIQUE(MovieID, CategoryName),
    CONSTRAINT cl_fk FOREIGN KEY (MovieID) REFERENCES MovieList(ID)
);

INSERT INTO CategoryList VALUES (1, 'Comedy');
INSERT INTO CategoryList VALUES (1, 'Romance');
INSERT INTO CategoryList VALUES (2, 'Romance');
INSERT INTO CategoryList VALUES (2, 'Drama');
INSERT INTO CategoryList VALUES (3, 'Documentary');
INSERT INTO CategoryList VALUES (4, 'Comedy');
INSERT INTO CategoryList VALUES (5, 'Comedy');
INSERT INTO CategoryList VALUES (5, 'Action');

質問

少なくともComedyRomance 両方のカテゴリに属するすべての映画を検索ます。

溶液

この質問は時々非常に難しいことがあります。 このようなクエリが答えと思われるかもしれません: -

SELECT  DISTINCT a.MovieName
FROM    MovieList a
        INNER JOIN CategoryList b
            ON a.ID = b.MovieID
WHERE   b.CategoryName = 'Comedy' AND
        b.CategoryName = 'Romance'

SQLFiddleデモ

これは決して非常に間違っています。なぜなら結果が出ないからです。 これの説明は、 各行CategoryName有効な値が1つしかないことです。 たとえば、最初の条件はtrueを返し、2番目の条件は常にfalseを返します 。 したがって、 AND演算子を使用するAND 、両方の条件が真でなければなりません。 それ以外の場合はfalseになります。 もう1つのクエリは次のようなものです。

SELECT  DISTINCT a.MovieName
FROM    MovieList a
        INNER JOIN CategoryList b
            ON a.ID = b.MovieID
WHERE   b.CategoryName IN ('Comedy','Romance')

SQLFiddleデモ

categoryName 少なくとも 1つの一致があるレコードと一致するため、結果はまだ間違っていcategoryName実際の解決策 は、映画ごとのレコードインスタンスの数を数えることです。 インスタンスの数は、条件で指定された値の総数と一致する必要があります。

SELECT  a.MovieName
FROM    MovieList a
        INNER JOIN CategoryList b
            ON a.ID = b.MovieID
WHERE   b.CategoryName IN ('Comedy','Romance')
GROUP BY a.MovieName
HAVING COUNT(*) = 2

SQLFiddleデモ(答え)

トリックNo.2( 各エントリの最大記録

与えられたスキーマ、

CREATE TABLE Software
(
    ID INT,
    SoftwareName VARCHAR(25),
    Descriptions VARCHAR(150),
    CONSTRAINT sw_pk PRIMARY KEY (ID),
    CONSTRAINT sw_uq UNIQUE (SoftwareName)  
);

INSERT INTO Software VALUES (1,'PaintMe','used for photo editing');
INSERT INTO Software VALUES (2,'World Map','contains map of different places of the world');
INSERT INTO Software VALUES (3,'Dictionary','contains description, synonym, antonym of the words');

CREATE TABLE VersionList
(
    SoftwareID INT,
    VersionNo INT,
    DateReleased DATE,
    CONSTRAINT sw_uq UNIQUE (SoftwareID, VersionNo),
    CONSTRAINT sw_fk FOREIGN KEY (SOftwareID) REFERENCES Software(ID)
);

INSERT INTO VersionList VALUES (3, 2, '2009-12-01');
INSERT INTO VersionList VALUES (3, 1, '2009-11-01');
INSERT INTO VersionList VALUES (3, 3, '2010-01-01');
INSERT INTO VersionList VALUES (2, 2, '2010-12-01');
INSERT INTO VersionList VALUES (2, 1, '2009-12-01');
INSERT INTO VersionList VALUES (1, 3, '2011-12-01');
INSERT INTO VersionList VALUES (1, 2, '2010-12-01');
INSERT INTO VersionList VALUES (1, 1, '2009-12-01');
INSERT INTO VersionList VALUES (1, 4, '2012-12-01');

質問

各ソフトウェアの最新バージョンを検索します。 次の列を表示します: SoftwareNameDescriptionsLatestVersionVersionNo列から )、 DateReleased

溶液

一部のSQL開発者は、誤ってMAX()集合関数を使用します。 彼らはこのように作成する傾向があり、

SELECT  a.SoftwareName, a.Descriptions,
        MAX(b.VersionNo) AS LatestVersion, b.DateReleased
FROM    Software a
        INNER JOIN VersionList b
            ON a.ID = b.SoftwareID
GROUP BY a.ID
ORDER BY a.ID

SQLFiddleデモ

ほとんどのRDBMSは、 group by節に集約されていない列を指定していないため、構文エラーを生成します )、結果は各ソフトウェアで正しいLatestVersionを生成しますが、明らかにDateReleasedは正しくありません。 MySQLは、 Window FunctionsCommon Table Expressionサポートしていませんが、一部のRDBMSはすでにそうしています。 この問題の回避策は、各ソフトウェアで個々の最大versionNoを取得し、後で他のテーブルに結合するsubqueryを作成することです。

SELECT  a.SoftwareName, a.Descriptions,
        b.LatestVersion, c.DateReleased
FROM    Software a
        INNER JOIN
        (
            SELECT  SoftwareID, MAX(VersionNO) LatestVersion
            FROM    VersionList
            GROUP BY SoftwareID
        ) b ON a.ID = b.SoftwareID
        INNER JOIN VersionList c
            ON  c.SoftwareID = b.SoftwareID AND
                c.VersionNO = b.LatestVersion
GROUP BY a.ID
ORDER BY a.ID

SQLFiddleデモ(答え)

そうだった。 私はもう一度MySQLタグに関する他のFAQを思い出して、もう一度投稿しMySQL 。 この小さな記事を読んでいただきありがとうございます。 私はあなたが少なくともこのことから知識を得たことを願っています。

更新1

トリックNo.3( 2つのID間の最新のレコードを見つける

与えられたスキーマ

CREATE TABLE userList
(
    ID INT,
    NAME VARCHAR(20),
    CONSTRAINT us_pk PRIMARY KEY (ID),
    CONSTRAINT us_uq UNIQUE (NAME)  
);

INSERT INTO userList VALUES (1, 'Fluffeh');
INSERT INTO userList VALUES (2, 'John Woo');
INSERT INTO userList VALUES (3, 'hims056');

CREATE TABLE CONVERSATION
(
    ID INT,
    FROM_ID INT,
    TO_ID INT,
    MESSAGE VARCHAR(250),
    DeliveryDate DATE
);

INSERT INTO CONVERSATION VALUES (1, 1, 2, 'hi john', '2012-01-01');
INSERT INTO CONVERSATION VALUES (2, 2, 1, 'hello fluff', '2012-01-02');
INSERT INTO CONVERSATION VALUES (3, 1, 3, 'hey hims', '2012-01-03');
INSERT INTO CONVERSATION VALUES (4, 1, 3, 'please reply', '2012-01-04');
INSERT INTO CONVERSATION VALUES (5, 3, 1, 'how are you?', '2012-01-05');
INSERT INTO CONVERSATION VALUES (6, 3, 2, 'sample message!', '2012-01-05');

質問

2人のユーザーの最新の会話を探します。

溶液

SELECT    b.Name SenderName,
          c.Name RecipientName,
          a.Message,
          a.DeliveryDate
FROM      Conversation a
          INNER JOIN userList b
            ON a.From_ID = b.ID
          INNER JOIN userList c
            ON a.To_ID = c.ID
WHERE     (LEAST(a.FROM_ID, a.TO_ID), GREATEST(a.FROM_ID, a.TO_ID), DeliveryDate)
IN
(
    SELECT  LEAST(FROM_ID, TO_ID) minFROM,
            GREATEST(FROM_ID, TO_ID) maxTo,
            MAX(DeliveryDate) maxDate
    FROM    Conversation
    GROUP BY minFROM, maxTo
)

SQLFiddleデモ


これは、あなたが物事を読んでいる間にテーブルを見つけることを望む:

jsfiddle

mysql> show columns from colors;                                                         
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+           
| id    | int(3)      | NO   | PRI | NULL    | auto_increment |
| color | varchar(15) | YES  |     | NULL    |                |
| paint | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+




select