by shigemk2

当面は技術的なことしか書かない

サブクエリの習作

MySQL

f:id:shigemk2:20140208011446j:plain

サンプルデータ 世界 - by shigemk2

ああ、めんどうなのでクエリしか載せない。

mysql> select * from Country where Code in (select CountryCode from CountryLanguage where Language = 'English' and IsOfficial = 'T') order by GNP desc limit 5;
+------+----------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+---------------------+----------------------------------------+----------------+---------+-------+
| Code | Name           | Continent     | Region                    | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP        | GNPOld     | LocalName           | GovernmentForm                         | HeadOfState    | Capital | Code2 |
+------+----------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+---------------------+----------------------------------------+----------------+---------+-------+
| USA  | United States  | North America | North America             |  9363520.00 |      1776 |  278357000 |           77.1 | 8510700.00 | 8110900.00 | United States       | Federal Republic                       | George W. Bush |    3813 | US    |
| GBR  | United Kingdom | Europe        | British Islands           |   242900.00 |      1066 |   59623400 |           77.7 | 1378330.00 | 1296830.00 | United Kingdom      | Constitutional Monarchy                | Elisabeth II   |     456 | GB    |
| CAN  | Canada         | North America | North America             |  9970610.00 |      1867 |   31147000 |           79.4 |  598862.00 |  625626.00 | Canada              | Constitutional Monarchy, Federation    | Elisabeth II   |    1822 | CA    |
| AUS  | Australia      | Oceania       | Australia and New Zealand |  7741220.00 |      1901 |   18886000 |           79.8 |  351182.00 |  392911.00 | Australia           | Constitutional Monarchy, Federation    | Elisabeth II   |     135 | AU    |
| HKG  | Hong Kong      | Asia          | Eastern Asia              |     1075.00 |      NULL |    6782000 |           79.5 |  166448.00 |  173610.00 | Xianggang/Hong Kong | Special Administrative Region of China | Jiang Zemin    |     937 | HK    |
+------+----------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+---------------------+----------------------------------------+----------------+---------+-------+

サブクエリを使った検索条件の設定 - データの取得 - MySQLの使い方

別テーブルから引っぱってきたデータを基にしてデータをとる。 公用語が英語の国のうち、GNPの金額が高い国のデータをとる。

IsOfficialをつけないと、なぜか(というかやはりというか)日本が出てくるんDA 国民のなかに英語をしゃべるひとがいるかららしい。