子查询

嵌套SELECT语句也叫子查询，一个 SELECT 语句的查询结果能够作为另一个语句的输入值。子查询不但能够出现在Where子句中，也能够出现在from子句中，作为一个临时表使用，也能够出现在select list中，作为一个字段值来返回。

select ename,deptno,sal
from emp
where deptno=(select deptno from dept where loc='NEW YORK')；

给出人口多于Russia(俄国)的国家名称

SELECT name FROM bbc
WHERE population>
(SELECT population FROM bbc
WHERE name='Russia')

给出'India'(印度), 'Iran'(伊朗)所在地区的任何国家的任何信息

SELECT * FROM bbc
WHERE region IN
(SELECT region FROM bbc
WHERE name IN ('India','Iran'))

给出人均GDP超过'United Kingdom'(英国)的欧洲国家

SELECT name FROM bbc
WHERE region='Europe' AND gdp/population >
(SELECT gdp/population FROM bbc
WHERE name='United Kingdom')

SELECT Score, (  
  SELECT COUNT(*)   
  FROM (  
    SELECT DISTINCT Score S   
    FROM Scores  
  ) AS Tmp  
  WHERE S >= Score  
) AS Rank  
FROM Scores  
ORDER BY Score DESC;