我得到了以下关系模式 -
country (countrycode,name,capital) &
population (populationcode,population,countrycode)
其中国家/地区代码是外键 w.r.t. 关系国家/地区。我必须投影人口最多的国家/地区,但聚合操作MAX(population)
并不直接适用于给定的命令 - 。
SELECT pcode, ccode, name, capital
FROM population NATURAL JOIN country
WHERE ((SELECT MAX(population) as maxpopulation FROM population NATURAL JOIN country))
对于给定的数据集 -
INSERT INTO country VALUES(1,'INDIA','NEW DELHI');
INSERT INTO country VALUES(2,'U.S.A.','WASHINGTON DC');
INSERT INTO country VALUES(3,'U.K.','LONDON');
INSERT INTO country VALUES(4,'CHINA','BEIJING');
INSERT INTO country VALUES(5,'JAPAN','TOKYO');
INSERT INTO population VALUES(1,1200000000,1);
INSERT INTO population VALUES(2,350000000,2);
INSERT INTO population VALUES(3,65640000,3);
INSERT INTO population VALUES(4,1300000000,4);
INSERT INTO population VALUES(5,127000000,5);
由于它在终端中产生以下输出 -
+-------+-------+--------+---------------+
| pcode | ccode | name | capital |
+-------+-------+--------+---------------+
| 1 | 1 | INDIA | NEW DELHI |
| 2 | 2 | U.S.A. | WASHINGTON DC |
| 3 | 3 | U.K. | LONDON |
| 4 | 4 | CHINA | BEIJING |
| 5 | 5 | JAPAN | TOKYO |
+-------+-------+--------+---------------+
5 rows in set (0.00 sec)
预期的输出是——
+-------+-------+--------+---------------+
| pcode | ccode | name | capital |
+-------+-------+--------+---------------+
| 4 | 4 | CHINA | BEIJING |
+-------+-------+--------+---------------+
1 rows in set (0.00 sec)
但它应该只告诉人口最多的国家的属性,即中国。
那么可以进行什么样的操作呢?
简单如下:
SELECT pcode, ccode, name, capital
FROM population NATURAL JOIN country
WHERE population = (SELECT MAX(population) FROM population)
不要使用 NATURAL JOIN
。 这是可憎的,因为它使用具有相同名称的密钥。 它甚至不使用正确声明的外键关系。
如果您只寻找一行,那么我建议您ORDER BY
和LIMIT
:
select p.populationcode, p.countrycode, c.name, c.capital
from population p join
country c
using (countrycode) -- of course, the traditional explicit "on" is totally correct too
order by p.population desc
limit 1;
当您使用比较最大总体的版本时,您可以获得重复项。 如果这是您想要的,那么一定要使用该版本。
在WHERE
中使用子查询并使用 LIMIT
函数。
SELECT p.populationcode, c.countrycode, c.name, c.capital
FROM population p
INNER JOIN country c ON p.countrycode = c.countrycode
WHERE (SELECT countrycode
FROM (SELECT countrycode, population
FROM population
ORDER BY population DESC LIMIT 1)) = c.countrycode