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 FROM

final WHERE final.gid = $1 ORDER BY final.pname) 

WHERE ORDER BY<--- 并将相同的最终值传递给它。我无法实施它。谁能帮忙？

Answer 1

仅假设select distinct&nbsp;case when inp = 1 then gid&nbsp; &nbsp; &nbsp; &nbsp;when inp = 2 then pid&nbsp; &nbsp; &nbsp; &nbsp;when inp = 3 then did&nbsp; end new_id,&nbsp; case when inp = 1 then gname&nbsp; &nbsp; &nbsp; &nbsp;when inp = 2 then pname&nbsp; &nbsp; &nbsp; &nbsp;when inp = 3 then name&nbsp; end as namefrom finalorder by 2DB 小提琴游乐场