英文:
PSQL queries creating multiple select queries based on condition to filter out values
问题
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;
我知道这不是正确的写法。
但这会给你一个我想做的事情的想法。
如果inp = 1
-> 获取唯一的gid和gname,并按gname排序
如果inp = 2
-> 获取唯一的pid和pname,其中gid = <用户提供的某个值>,并按pname排序。
我想在psql中编写这个逻辑。
我知道CASE WHEN
只能有一列,所以我必须为单个final.inp=1
创建两个CASE
,分别用于name和id。
由于这部分,我无法编写这个逻辑
(SELECT DISTINCT(final.pid) AS id, final.pname as name FROM
final WHERE final.gid = $1 ORDER BY final.pname)
WHERE ORDER BY
<--- 并将相同的final值传递给它。
我无法实现它。有人可以帮忙吗?
英文:
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
)
Now I want to write a query depending on the inp column value
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;
I know this is not a correct way of writing it.
But this will give you an idea what I am trying to do.
if inp = 1
-> get unique gid's and gname and order by gname
if inp = 2
-> get unique pid's and pname where gid = <some value provided by user> and order by pname.
I want to write this logic in psql.
I know case When
can have only one column so I have to create two cases for a single final.inp=1
for name and id.
I ain't able to write this logic because of this part
(SELECT DISTINCT(final.pid) AS id, final.pname as name FROM
final WHERE final.gid = $1 ORDER BY final.pname)
WHERE ORDER BY
<--- and passing the same final value to it.
I am not able to implement it. Can anyone help?
答案1
得分: 2
只有一个假设
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
英文:
Only assumption
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论