以下のようなテーブルがあり、ドイツ語を母国語としている国を選択しドイツ語を
利用している人口を合計して算出する場合の例

スカラー【scalar】 別ウィンドウで表示
長さ・面積・質量・温度・時間など、大きさだけで定まる数量。スカラー量

■ドイツ語を利用しているCountryCode 、IsOfficial 、Percentage を確認
mysql> select * from CountryLanguage where Language = 'German'
-> and IsOfficial = 'T';

+————-+———-+————+————+
| CountryCode | Language | IsOfficial | Percentage |
+————-+———-+————+————+
| AUT | German | T | 92.0 |
| LIE | German | T | 89.0 |
| DEU | German | T | 91.3 |
| CHE | German | T | 63.6 |
| BEL | German | T | 1.0 |
| LUX | German | T | 2.3 |
+————-+———-+————+————+
6 rows in set (0.01 sec)

■西ヨーロッパの国々のリスト
mysql> select Code,Name,Population from Country where Region = 'Western Europe';
+——+—————+————+
| Code | Name | Population |
+——+—————+————+
| NLD | Netherlands | 15864000 |
| BEL | Belgium | 10239000 |
| AUT | Austria | 8091800 |
| LIE | Liechtenstein | 32300 |
| LUX | Luxembourg | 435700 |
| MCO | Monaco | 34000 |
| FRA | France | 59225700 |
| DEU | Germany | 82164700 |
| CHE | Switzerland | 7160400 |
+——+—————+————+
9 rows in set (0.00 sec)

■西ヨーロッパの国々からドイツ語をオフィシャル言語として利用している国を確認。
mysql> select Code Name,Population from Country where Region = 'Western Europe'
-> and Code IN
-> (select CountryCode from CountryLanguage where Language = 'German'
-> and IsOfficial = 'T');

+——+————+
| Name | Population |
+——+————+
| BEL | 10239000 |
| AUT | 8091800 |
| LIE | 32300 |
| LUX | 435700 |
| DEU | 82164700 |
| CHE | 7160400 |
+——+————+
6 rows in set (0.00 sec)

mysql>

■ドイツ語をオフィシャル言語としている国の総人口を見てみる。
mysql> select sum(Population) as 'German Speaker' from Country where
-> Region = 'Western Europe' and Code IN
-> (select CountryCode from CountryLanguage where Language = 'German'
-> and IsOfficial = 'T');

+—————-+
| German Speaker |
+—————-+
| 108123900 |
+—————-+
1 row in set (0.01 sec)


select sum(Population) as 'Not Only German Speakers' from Country where
Region = 'Western Europe' and Code IN
(select CountryCode from CountryLanguage where Language = 'German'
and IsOfficial = 'T');

■ドイツ語をオフィシャル言語としている国の中で、ドイツ語を利用している人口を確認してみる。

SELECT SUM(Speakers) AS German_Speakers
FROM (SELECT (C.Population * CL.Percentage) / 100 AS Speakers
FROM CountryLanguage CL, Country C
WHERE CL.CountryCode = C.Code
AND C.Region = 'Western Europe'
AND CL.Language = 'German'
) AS T_German_Speakers;


SELECT ROUND(SUM(Speakers)) AS German_Speakers
FROM (SELECT (C.Population * CL.Percentage) / 100 AS Speakers
FROM CountryLanguage CL, Country C
WHERE CL.CountryCode = C.Code
AND C.Region = 'Western Europe'
AND CL.Language = 'German'
) AS T_German_Speakers;

round

参考サイト
12.2.8.1. スカラ演算子としてのサブクエリ
10.2. 数値タイプ


GROUP_CONCAT、サブクエリーを利用してデータの抽出を行い
CONCATを用いて文字列を連結する。

CONCAT(str1,str2,…)

引数を連結した結果であるストリングを戻します。ひとつ以上の引数を持つ場合があります。
すべての引数が非バイナリ ストリングである場合、結果は非バイナリ ストリングになります。
引数がひとつでもバイナリ ストリングを含む場合は、結果はバイナリ ストリングになります。
数値の引数はそれに等しいバイナリ ストリング形態に変換されます。それを避けたい場合は、
次の例のように、明示的なタイプ キャストを使用することができます :

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

サンプルテーブル
sample

サンプルデータ
sample_data

上記のテーブルからオフィシャル言語が多い国を抽出してみる。


select B.NAME,Count(*) SUM from
CountryLanguage A,Country B
where A.CountryCode = B.Code
AND A.ISOfficial = 'T'
GROUP BY B.Name
Order by SUM desc
limit 1;

select_official

select B.NAME,Count(*) SUM,
GROUP_CONCAT(Language) LANG
from CountryLanguage A,Country B
where A.CountryCode = B.Code
AND A.ISOfficial = 'T'
GROUP BY B.Name
ORDER BY SUM DESC
limit 1;

gc1


SELECT TMP.NAME,TMP.SUM,TMP.LANG FROM
(
select B.NAME,Count(*) SUM,
GROUP_CONCAT(Language) LANG
from CountryLanguage A,Country B
where A.CountryCode = B.Code
AND A.ISOfficial = 'T'
GROUP BY B.Name
ORDER BY SUM DESC
limit 1
) AS TMP;

gc2

上記結果をCONCATで結合して文言にする。

SELECT
CONCAT(
'The country ',
(SELECT Name FROM (
SELECT Name, COUNT(*) AS nlanguages,
GROUP_CONCAT(Language) as languages
FROM Country c, CountryLanguage cl
WHERE c.Code = cl.CountryCode
AND cl.IsOfficial = 'T'
GROUP BY Name
ORDER BY nlanguages DESC, Name
LIMIT 1
) AS tmp
),
' has ',
(SELECT nlanguages FROM (
SELECT Name, COUNT(*) AS nlanguages,
GROUP_CONCAT(Language) as languages
FROM Country c, CountryLanguage cl
WHERE c.Code = cl.CountryCode
AND cl.IsOfficial = 'T'
GROUP BY Name
ORDER BY nlanguages DESC, Name
LIMIT 1
) AS tmp1
),
' official languages: ',
(SELECT languages FROM (
SELECT Name, COUNT(*) AS nlanguages,
GROUP_CONCAT(Language) as languages
FROM Country c, CountryLanguage cl
WHERE c.Code = cl.CountryCode
AND cl.IsOfficial = 'T'
GROUP BY Name
ORDER BY nlanguages DESC, Name
LIMIT 1
) AS tmp2
)
) AS '文字の結合';

concat_sentenct

参考サイト
11.11.1. GROUP BY (Aggregate) Functions


11.3. 文字列関数


サブクエリーのSELECTステートメントを利用して次のテーブルから,Western Europeの中で
ドイツ語を母国語とする人口を算出するクエリーサンプル。

テーブルサンプル
subquery_select

Country には人口
CountryLanguageには言語毎の割合が%で入っている


select sum(tmp.German_Speakers) from
(
select (A.Population * B.Percentage) / 100 as 'German_Speakers'
from Country A, CountryLanguage B
where A.Region = 'Western Europe'
and A.Code = B.CountryCode
and B.Language = 'German'
) as tmp;

population

参考サイト

12.2.9.1. The Subquery as Scalar Operand


12.2.8.5. 行サブクエリ

この時点までの話は、単一値や値のカラムを返すサブクエリなどのような、
スカラやカラム サブクエリに関しての物でした。行サブクエリ は単列を戻し、
ひいては複数のカラム値を返す事ができるサブクエリ異型です。ここに2つ例があります。

SELECT * FROM t1 WHERE (1,2) = (SELECT column1, column2 FROM t2);
SELECT * FROM t1 WHERE ROW(1,2) = (SELECT column1, column2 FROM t2);

もしテーブル t2 が column1 = 1 と column2 = 2 の場所に行を持っていれば、
ここにあるクエリは両方 TRUE です。

select_pop

Houstonの人口をシンプルなクエリーと行コンストラクタを利用して抽出してみました。
■シンプル
select Population from City where Name = ‘Houston’;
■行コンストラクタ
select Population from City where (Name,District,CountryCode) = (‘Houston’,’Texas’,’USA’);

row_contructor

参考サイト

12.2.8.5. 行サブクエリ


比較演算子の後に続かなければいけない ANY キーワードは、
「もしサブクエリが返すカラム内の値の ANY に対する比較が TRUE であれば、
TRUE を返す」 という事を意味します。

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

(例) SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

テーブル (10) を含むテーブル t1 内に行があると仮定してください。
10 以下である値 7 が t2 の中にあるので、もしテーブル t2 が (21,14,7) を含むなら、
その式は TRUE です。もしテーブル t2 が (20,10) を含むか、テーブル t2 が空であれば、
その式は FALSE です。もしテーブル t2 が (NULL,NULL,NULL) を含むなら、その式は
UNKNOWN です。

サブクエリと共に利用される時、IN という言葉は = ANY のエイリアスとなります。
従って、これら2つのステートメントは同じになります。


SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

■GROUP BYでまとめず単純に平均面積より大きいデータを選択した場合。

SELECT Continent, Name
FROM Country
WHERE SurfaceArea > (SELECT AVG(SurfaceArea)
FROM Country );

select_any_group_by

■”GROUP BY Continent”でANYを付けずにSELECTした場合は、複数列が返って
きてエラーになります。ANYを付ける事で複数行が返ってくるサブクエリーの処理も
問題無く処理できます。


SELECT Continent, Name
FROM Country
WHERE SurfaceArea > ANY (SELECT AVG(SurfaceArea)
FROM Country
GROUP BY Continent
);

select_any

    参考サイト


12.2.8.3. ANY、IN、そして SOME を持つサブクエリ

D.3. サブクエリの規制


構文

ALL ()

ALL という語は比較演算子の後に指定するもので、“サブクエリが返すレコードの
ALL(すべて)に対して比較が TRUE の場合 TRUE を返す” ことを表します。

(例)
このQueryは、同じ大陸に存在する国で一番人口の多い国を結果として返す。


mysql> SELECT Continent, Name
-> FROM Country c1
-> WHERE Population >= ALL (SELECT Population
-> FROM Country c2
-> WHERE c1.Continent=c2.Continent
-> );

ALLをつけないと複数列が返るのでエラーになる。
sub_all

参考サイト
6.4.2.4. ALL とともに使用したサブクエリ

6.4.2. サブクエリ構文

サブクエリ (この記事は4.1版の新しい機能を紹介する6番目の記事)