sql - PostgreSQL 错误 : Subquery has too many columns

Question

我的 PostgreSQL 数据库中有一个表 my_table，其中包含如下三列:

gid id  max_height
3   1   19.3
3   2   19.3
3   3   20.3
3   4   20.3
3   5   19.3
3   6   19.3
3   7   21.4
3   8   21.4
3   9   21.4
3   10  21.4
3   11  21.4
3   12  21.4
22  1   23.1
22  2   23.1
22  3   23.1
22  4   23.1
22  5   23.1
22  6   23.1
22  7   22.1
22  8   22.1
22  9   22.1
22  10  22.1
22  11  22.1
22  12  22.1
29  1   24
29  2   24
29  3   24
29  4   18.9
29  5   18.9
29  6   18.9
29  7   NULL
29  8   NULL
29  9   27.1
29  10  27.1
29  11  6.5
29  12  6.5

对于每个 gid 组，有 12 个值(id 和 max_height)。我正在尝试从 my_table 中选择 max_height 并尝试与子查询中的那些进行比较。代码是:

SELECT
gid,
max_height
FROM
my_table
where max_height not in
(
SELECT
gid, max_height
FROM
-- this part selects the most repeated max_height from my_table
(
SELECT gid, max_height,
ROW_NUMBER() OVER (PARTITION BY gid ORDER BY freq DESC) AS rn
    FROM (
        SELECT gid, max_height, COUNT(id) AS freq
        FROM my_table
        GROUP BY 1, 2
        )hgt_freq
) ranked_hgt_req
WHERE rn = 1
)

我得到了

ERROR: subquery has too many columns at Where max_height NOT IN

任何人都可以帮助我了解我犯的错误或可以帮助我解决错误吗？

Answer 1

  SELECT
    gid,
    max_height
    FROM
    my_table
    where max_height not in
    (
    SELECT
      max_height  /* you must select only a column do the fact in where you have  a column */
    FROM
    -- this part selects the most repeated max_height from my_table
    (
    SELECT gid, max_height,
    ROW_NUMBER() OVER (PARTITION BY gid ORDER BY freq DESC) AS rn
        FROM (
            SELECT gid, max_height, COUNT(id) AS freq
            FROM my_table
            GROUP BY 1, 2
            )hgt_freq
    ) ranked_hgt_req
    WHERE rn = 1
    )