FROM 子句中的子查询

SQL 数据连接

Maham Faisal Khan

Senior Content Developer, DataCamp

FROM 子句中的子查询

SELECT continent, MAX(indep_year) AS most_recent
FROM states
GROUP BY continent;

| continent     | most_recent  |
| --------------|------------- |
| Asia          |        1984  |
| Europe        |        1814  |
| Oceania       |        1901  |
| North America |        1776  |
| South America |        1818  |

聚焦于 monarchs 表中的记录

SELECT left_table.id, left_val
FROM left_table, right_table
WHERE left_table.id = right_table.id

SELECT DISTINCT left_table.id, left_val
FROM left_table, right_table
WHERE left_table.id = right_table.id

完成子查询

-- 查询：返回有君主的洲及最近独立年份

SELECT DISTINCT monarchs.continent, sub.most_recent

FROM monarchs,    
    (SELECT 
        continent, 
        MAX(indep_year) AS most_recent
     FROM states
     GROUP BY continent) AS sub
WHERE monarchs.continent = sub.continent     

ORDER BY continent;

| continent   | most_recent  |
|-------------|--------------|
| Asia        |        1984  |
| Europe      |        1814  |

让我们来练习！

SQL 数据连接