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

huangapple go评论83阅读模式
英文:

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
)


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

现在我想根据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 &#39;a&#39; AS gname, &#39;b&#39; AS pname, &#39;c&#39; AS name, 1 AS gid, 2 AS pid, 3 AS did, 3 AS inp
	UNION
	SELECT &#39;a1&#39; AS gname, &#39;b1&#39; AS pname, &#39;c1&#39; AS name, 11 AS gid, 21 AS pid, 31 AS did, 3 AS inp
)

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

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

DB Fiddle playground

英文:

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

DB Fiddle playground

huangapple
  • 本文由 发表于 2022年6月26日 19:30:19
  • 转载请务必保留本文链接:https://go.coder-hub.com/72761180.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定