PSQL 查询根据条件创建多个选择查询以过滤掉值

Question

WITH final AS (    SELECT 'a' AS gname, 'b' AS pname, 'c' AS name, 1 AS gid, 2 AS pid, 3 AS did, 3 AS inp    UNION    SELECT 'a1' AS gname, 'b1' AS pname, 'c1' AS name, 11 AS gid, 21 AS pid, 31 AS did, 3 AS inp)现在我想根据 inp 列值编写一个查询SELECT     (CASE final.inp     WHEN 1        THEN         (SELECT DISTINCT(final.gid) AS id, final.gname as name FROM        final ORDER BY final.gname)    WHEN 2        THEN         (SELECT DISTINCT(final.pid) AS id, final.pname as name FROM        final WHERE final.gid = $1 ORDER BY final.pname)    WHEN 3         THEN         (SELECT DISTINCT(final.did) AS id, final.name as name FROM        final WHERE final.pid = $1 ORDER BY final.name)    END)        FROM (%s) final;我知道这不是正确的写法。但这会让您了解我正在尝试做什么。if inp = 1-> 获取唯一的 gid 和 gname 并按 gname 排序如果inp = 2-> 获取唯一的 pid 和 pname，其中 gid = 并按 pname 排序。我想在 psql 中编写这个逻辑。我知道案例When只能有一列，所以我必须final.inp=1为名称和 ID 创建两个案例。由于这部分，我无法编写此逻辑(SELECT DISTINCT(final.pid) AS id, final.pname as name FROMfinal WHERE final.gid = $1 ORDER BY final.pname) WHERE ORDER BY<--- 并将相同的最终值传递给它。我无法实施它。谁能帮忙？

Answer 1

仅假设

select distinct 

case when inp = 1 then gid

       when inp = 2 then pid

       when inp = 3 then did

  end new_id,

  case when inp = 1 then gname

       when inp = 2 then pname

       when inp = 3 then name

  end as name

from final

order by 2

DB 小提琴游乐场