SQL Oracle:如何在该列中查找记录中记录特定ID的记录 [英] SQL Oracle : how to find records maching a particular id in the column

问题描述

我的查询是:

select A.* 
  from (select r.role_id, 
               r.role_name, 
               r.active, 
               decode( r.entity_type_id, 1000, m.name, 3000, cour.name, 4000, 'Ensenda' ) companyName,
               LISTAGG(p.permission_id, ' | ') WITHIN GROUP (ORDER BY r.role_id) permission_id, 
               LISTAGG(p.permission_name, ' | ') WITHIN GROUP (ORDER BY r.role_id) permission_name, 
               row_number() over (order by r.created_ts desc) as RN, 
               count(*) over () as TOTAL_ROWS, 
               r.created_ts roleCreated 
          from t_role r
          left join t_role_permission rp ON r.role_id = rp.role_id
          left join t_permission p ON rp.permission_id = p.permission_id
          left join merchant m on r.entity_id = m.merchantkey 
          left join courier cour on r.entity_id = cour.courierkey 
         where 1=1
         --and p.permission_id =301446
         group by r.role_id, r.role_name, r.active, r.created_ts,
               decode( r.entity_type_id, 1000, m.name, 3000, cour.name, 4000, 'Ensenda' ) 
      ) A 
where RN between 1 and 100 order by roleCreated desc

我的结果是:

现在，我要基于permission_id过滤列表，这样，我想要在Permission_id列中匹配该Permission_id的所有记录的列表.

Now I want to filter the list on the basis of permission_id in such a way that, I want list of all the records matching that permission_id in permission_id column.

例如:假设我为permission_id= 301446做一个过滤器.我想要以下结果

Eg: suppose I do a filter for permission_id= 301446. I want the following result

PS:在where子句中添加和p.permission_id=301446不会产生预期的结果.

PS: adding and p.permission_id=301446 in where clause will not produce desired result.

推荐答案

基于PasserBy评论的答案

Answer based on PasserBy comment

select A.* from(select r.role_id, r.role_name, r.active, 
decode( r.entity_type_id, 1000, m.name, 3000, cour.name, 4000, 'Ensenda' ) companyName,
LISTAGG(p.permission_id, ' | ') WITHIN GROUP (ORDER BY r.role_id) permission_id, 
LISTAGG(p.permission_name, ' | ') WITHIN GROUP (ORDER BY r.role_id) permission_name, 
row_number() over (order by r.created_ts desc) as RN, count(*) over () as TOTAL_ROWS, r.created_ts roleCreated 
from t_role r
left join t_role_permission rp ON r.role_id = rp.role_id
left join t_permission p ON rp.permission_id = p.permission_id
left join merchant m on r.entity_id = m.merchantkey 
left join courier cour on r.entity_id = cour.courierkey 
where 1=1

group by r.role_id, r.role_name, r.active, r.created_ts,
decode( r.entity_type_id, 1000, m.name, 3000, cour.name, 4000, 'Ensenda' ) 
)A where RN between 1 and 100 
and REGEXP_LIKE(a.permission_id,'(^|\s)301446(\s|$)')
order by roleCreated desc;

这篇关于SQL Oracle:如何在该列中查找记录中记录特定ID的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！