SQL：子查询的列过多 [英] SQL: subquery has too many columns

问题描述

我正在尝试使用postgresql进行查询。
数据库包含两个关系：王国（其中包括一些英国国王）和王朝（其中包含来自斯图尔特王朝的一些人）

I'm trying to make a query with postgresql. The database contains two relations: "kingdom", which includes some english kings, and "dinasty", which contains some people from the stuart dinasty

王国包括国王的名字以及他的王国开始和结束的时间。 身份关系包括姓名，性别，出生和死亡。

The relation "kingdom" includes the king name and when his kingdom started and ended. The relation "dinasty" includes name, gender, birth and death.

我要查询的是国王去世时年龄最大的国王。

What I'm trying to query is the king that was the oldest one when he died.

对于我的查询，我在第3行（NO IN IN）收到此错误：子查询的列太多

With my query I'm receiving this error at LINE 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里面的内容是正确的。

What is inside the NOT IN is correct.

推荐答案

您要在子查询中投影三个列，但在一个 > IN 子句。在子查询中仅为 IN 选择所需的列（ r1.king ）：

You are projecting three columns in your subquery, but comparing a single one of them in the IN clause. Select just the required column (r1.king) for the IN in the subquery:

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
);

这篇关于SQL：子查询的列过多的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！