HAVING以下が理解できません

Question

『達人に学ぶ SQL徹底指南書』でSQLの勉強をし始めたばかりです。
P.134のSQL文でHAVING以下が理解できず躓いています。

テーブル名：SupParts
列はsup、partの2列で14行
sup | part
A ボルト
A ナット
A パイプ
B ボルト
B パイプ
C ボルト
C ナット
C パイプ
D ボルト
D パイプ
E ヒューズ
E ナット
E パイプ
F ヒューズ

CREATE TABLE SupParts
(sup CHAR(32) NOT NULL,
part CHAR(32) NOT NULL,
PRIMARY KEY(sup, part));

INSERT INTO SupParts VALUES('A', 'ボルト');
INSERT INTO SupParts VALUES('A', 'ナット');
INSERT INTO SupParts VALUES('A', 'パイプ');
INSERT INTO SupParts VALUES('B', 'ボルト');
INSERT INTO SupParts VALUES('B', 'パイプ');
INSERT INTO SupParts VALUES('C', 'ボルト');
INSERT INTO SupParts VALUES('C', 'ナット');
INSERT INTO SupParts VALUES('C', 'パイプ');
INSERT INTO SupParts VALUES('D', 'ボルト');
INSERT INTO SupParts VALUES('D', 'パイプ');
INSERT INTO SupParts VALUES('E', 'ヒューズ');
INSERT INTO SupParts VALUES('E', 'ナット');
INSERT INTO SupParts VALUES('E', 'パイプ');
INSERT INTO SupParts VALUES('F', 'ヒューズ');

★求めるものは、数も種類も全く同じ部品を取り扱う供給業者のペアです。

★解答
SELECT SP1.sup, SP2.sup
FROM SupParts SP1, SupParts SP2
WHERE SP1.sup < SP2.sup /* 業者の組み合わせを作る /
AND SP1.part = SP2.part / 条件１．同じ種類の部品を扱う /
GROUP BY SP1.sup, SP2.sup
HAVING COUNT() = (SELECT COUNT() / 条件２．同数の部品を扱う /
FROM SupParts SP3
WHERE SP3.sup = SP1.sup)
AND COUNT() = (SELECT COUNT(*)
FROM SupParts SP4
WHERE SP4.sup = SP2.sup);

★疑問点
HAVING句の前まで
SELECT SP1.sup, SP2.sup
FROM SupParts SP1, SupParts SP2
WHERE SP1.sup < SP2.sup /* 業者の組み合わせを作る /
AND SP1.part = SP2.part / 条件１．同じ種類の部品を扱う */
GROUP BY SP1.sup, SP2.sup
で
s1 s2
A B
A C
A D
A E
B C
B D
B E
C D
C E
D E
E F
の11列になることは理解できました。

また最初のSELECT句に　count() を加えると
s1 s2 count()
A B 2
A C 3
A D 2
A E 2
B C 2
B D 2
B E 1
C D 2
C E 2
D E 1
E F 1
となるのですが、これは以降のHAVING句を理解する手立てになるのでしょうか？

大変恐縮ですが、ご教授いただけるとありがたいです！
宜しくお願いいたします！！

Answer 1

全てのDBMSで動くわけではありませんが、MySQL5.6でのヒントになるクエリです。

SELECT SP1.sup, SP2.sup, COUNT(*) AS COUNT,
(
  SELECT COUNT(*)
  FROM SupParts SP3 
  WHERE SP3.sup = SP1.sup
) AS COUNT_LEFT,
(
  SELECT COUNT(*) 
  FROM SupParts SP4 
  WHERE SP4.sup = SP2.sup
) AS COUNT_RIGHT
FROM SupParts SP1, SupParts SP2 
WHERE SP1.sup < SP2.sup
AND SP1.part = SP2.part
GROUP BY SP1.sup, SP2.sup

By.SQL Fiddle

Comments

[Hekomi]

ありがとうございます！
感動しました！

COUNT、COUNT_LEFT、COUNT_RIGHT
がすべて等しい数値になるものを選ぶということですね！

sup sup COUNT COUNT_LEFT COUNT_RIGHT
A B 2 3 2
A C 3 3 3
A D 2 3 2
A E 2 3 3
B C 2 2 3
B D 2 2 2
B E 1 2 3
C D 2 3 2
C E 2 3 3
D E 1 2 3
E F 1 3 1

助かりました！　
本当にありがとうございました！

[defghi1977]

やっぱSQLは言葉で表現するもんじゃねーな…

Answer 2

まず, HAVING句直前までのクエリを実行してみましょう

SELECT SP1.sup, SP2.sup, SP1.part
FROM SupParts SP1, SupParts SP2 
WHERE SP1.sup < SP2.sup              /* 業者の組み合わせを作る */
AND SP1.part = SP2.part;           /* 条件１．同じ種類の部品を扱う */

するとサプライヤAとサプライヤBに共通する部品のリストが抽出されます.

このクエリから部品を省略しGROUP BY句を適用し, サプライヤの組み合わせ毎の取扱い部品数を計算可能としたのが次のクエリです.

SELECT SP1.sup, SP2.sup, COUNT(*)
FROM SupParts SP1, SupParts SP2 
WHERE SP1.sup < SP2.sup              /* 業者の組み合わせを作る */
AND SP1.part = SP2.part            /* 条件１．同じ種類の部品を扱う */
GROUP BY SP1.sup, SP2.sup;

ここで更にHAVING句内のサブクエリで「サプライヤAの取り扱っている部品数＝リストのcount()」かつ「サプライヤBの取り扱っている部品数＝リストのcount()」なるサプライヤの組み合わせを抽出しています.

SELECT SP1.sup, SP2.sup, COUNT(*)
FROM SupParts SP1, SupParts SP2 
WHERE SP1.sup < SP2.sup              /* 業者の組み合わせを作る */
AND SP1.part = SP2.part            /* 条件１．同じ種類の部品を扱う */
GROUP BY SP1.sup, SP2.sup 
HAVING COUNT(*) = (SELECT COUNT(*)    /* 条件２．同数の部品を扱う */
FROM SupParts SP3 
WHERE SP3.sup = SP1.sup)
AND COUNT(*) = (SELECT COUNT(*) 
FROM SupParts SP4 
WHERE SP4.sup = SP2.sup); 

すると結果は「サプライヤAの取り扱っている部品数＝サプライヤBの取り扱っている部品数」となり, 目的の「数も種類も全く同じ部品を取り扱う供給業者のペア」が求められたことになります.

Comments

[Hekomi]

早々にご回答いただき、本当にありがとうございます！！
GROUP　BY句を適用するところまでは、理解できました！

しかし、やはり最後のHAVINGの部分の理解に苦しんでおります(>_<)

＜引用＞
ここで更にHAVING句内のサブクエリで「サプライヤAの取り扱っている部品数＝リストのcount(*)」かつ「サプライヤBの取り扱っている部品数＝リストのcount(*)」なるサプライヤの組み合わせを抽出しています.

リストのcount(*)というのは、GROUP　BY句まででできた
s1 s2
A B
A C
A D
A E
B C
B D
B E
C D
C E
D E
E F
のカウントという解釈でよろしかったでしょうか？

このcount(*)は具体的には、どういう数値になるのでしょうか？

また
サブクエリ
(SELECT COUNT(*)
FROM SupParts SP3
WHERE SP3.sup = SP1.sup)
がなぜ「サプライヤAの取り扱っている部品数」になり
(SELECT COUNT(*)
FROM SupParts SP4
WHERE SP4.sup = SP2.sup);
がなぜ「サプライヤBの取り扱っている部品数」になるのでしょうか？

初心者であり的外れな質問になってしまっているかとは存じますが
ご教授いただけるとありがたいです。

[defghi1977]

まあSQLの中でも自己結合は鬼門中の鬼門なので混乱するのも致し方無しとは思います.
クエリにCOUNT(*)を追加して実行してみると判りやすくなりますよ.

> また
サブクエリ
(SELECT COUNT(*)
FROM SupParts SP3
WHERE SP3.sup = SP1.sup)
がなぜ「サプライヤAの取り扱っている部品数」になり
(SELECT COUNT(*)
FROM SupParts SP4
WHERE SP4.sup = SP2.sup);
がなぜ「サプライヤBの取り扱っている部品数」になるのでしょうか？

上で
FROM SupParts SP1
とあるので, サブクエリ中の"SP1.sup"は固定値みたいなものです. なので, クエリ中のサプライヤとSupPartsテーブル内のサプライヤとを比較し, COUNT(*)で一致したレコード数を求めています. つまり, これはサプライヤが取り扱っている部品数になります.

[Hekomi]

ご回答、ありがとうございます！
鬼門中の鬼門なのですね((+_+))
これからも、しばらく苦難の道が予想されます…。
またお助けいただければありがたいです！

[defghi1977]

使いこなせるようになるとこれほど心強いものはないのですが, いかんせんさっきのあなたのようにSQLの意味について周りに解説するのが非常に辛いという諸刃の剣です. （理解には多分に数学的なセンスが必要となるので）
とは言え, 難しいSQLを読めるようになると大抵のSQLはすらすらと読み解けるようになるのでトレーニングとしては良い題材かもしれません.

[Hekomi]

早々にご回答いただき、本当に心強かったです！
数学的センスに関しては残念な身ですが
精進していきたいと思います！
ありがとうございました！！

Answer 3

答えになるかわからないですが、
havingは、whereとgroup byの後に実行されるので、count(*)はその件数になるかと思います。

Comments

[Hekomi]

初心者の拙い質問にご回答いただき
ありがとうございます！
どうにか、理解することができました！

Answer 4

SQLを実行できる環境は用意されているでしょうか？

達人に学ぶ SQL徹底指南書

SQL入門用としては難し過ぎるかと。
SQL入門を何か月やってから中級編として使っては？

Comments

[Hekomi]

ご回答いただき、ありがとうございます！
MySQL　Workbenchを使って勉強をすすめております。
残念ながらご指摘通り、躓いてばかりです。
「スッキリわかるSQL入門　ドリル215問付き」も併用しながら
学習を進めております。
また躓き凹んでいた際には助けていただけると嬉しいです！