Postgresql SQL：子查询的列太多

发布于09月03日

我正在try 用postgresql进行查询.

"王国"的关系包括国王的名字以及他的王国开始和结束的时间."日常"关系包括姓名、性别、出生和死亡.

我想问的是国王，他go 世时是最年长的一位.

通过我的查询，我在第3行(不在)收到了这个错误: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
);

不在里面的东西是正确的.

推荐答案

在子查询中投影three列，但在IN子句中比较其中的one列.仅为子查询中的IN Select 所需的列(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
);