SQL:子查询的列太多

Question

我正在尝试使用 postgresql 进行查询。数据库包含两个关系:“kingdom”，其中包括一些英国国王，以及“dinasty”，其中包含一些来自斯图尔特王朝的人

“王国”关系包括国王的名字以及他的王国开始和结束的时间。 “朝代”关系包括姓名、性别、出生和死亡。

我想查询的是死时最年长的国王。

对于我的查询，我在第 3 行(NOT IN)收到此错误:subquery has too many columns

这是查询:

SELECT kingdom.king, dinasty.birth, dinasty.death
FROM kingdom, dinasty
WHERE kingdom.king = dinasty.name AND kingdom.king NOT IN
(
    SELECT DISTINCT R1.king, R1.birth, R1.death
    FROM
    (
        SELECT DISTINCT R1.king, D1.birth, D1.death
        FROM kingdom AS R1, dinasty AS D1, dinasty AS D2
        WHERE R1.king=D1.name
    ) AS R1, 
    (
        SELECT DISTINCT R1.king, D1.birth, D1.death
        FROM kingdom AS R1, dinasty AS D1, dinasty AS D2
        WHERE R1.king=D1.name
    ) AS R2
    WHERE R1.death-R1.birth < R2.death-R2.birth
);

NOT IN里面的内容是正确的。

Answer 1

您在子查询中投影了 三 列，但在 IN 子句中比较了其中的一个 一 列。只为子查询中的 IN 选择所需的列 (r1.king):

SELECT kingdom.king, dinasty.birth, dinasty.death
FROM kingdom, dinasty
WHERE kingdom.king = dinasty.name AND kingdom.king NOT IN
(
    SELECT DISTINCT R1.king
    FROM
    (
        SELECT DISTINCT R1.king, D1.birth, D1.death
        FROM kingdom AS R1, dinasty AS D1, dinasty AS D2
        WHERE R1.king=D1.name
    ) AS R1, 
    (
        SELECT DISTINCT R1.king, D1.birth, D1.death
        FROM kingdom AS R1, dinasty AS D1, dinasty AS D2
        WHERE R1.king=D1.name
    ) AS R2
    WHERE R1.death-R1.birth < R2.death-R2.birth
);