SQL Oracle: 如何在列中查找与特定ID匹配的记录

Question

我的查询是：

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筛选列表，以便获得所有匹配权限ID列中该权限ID的记录列表。

例如：假设我筛选permission_id= 301446。我想要以下结果： 提示：在where子句中添加p.permission_id=301446将无法产生所需的结果。

- Bhuvan

---

AND p.permission_id LIKE '%301446%' 会产生不想要的结果吗？ - Passerby

Passerby提供了解决方案，但是你这里的数据库设计很糟糕，应该进行更正。当你更正设计后，问题也会消失。 - Germann Arlington

@路人甲：不，这并没有解决我的问题。我想要类似于“包含”子句的东西。 - Bhuvan

@bhuvan 如果你想要 fancy 的 "contains"，你可能需要使用 REGEXP_LIKE：AND REGEXP_LIKE(p.permission_id,'(^|\s)301446(\s|$)')。 - Passerby

Answer 1

基于路人评论的答案

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;

- Bhuvan

Answer 2

对于一个大数据集，其中你要查找的permission_id的值不常见，查询整个数据集然后在聚合结果上进行过滤将非常低效，并且似乎是一个逻辑上不正确的结果。

你要查找的不是包含特定值的permission id列表的角色列表，而是包含特定权限的角色的permission id列表。从逻辑上讲，最终的结果是相同的，但是更精确地匹配所提出问题的逻辑实现似乎总是带来可读性和（奇怪的）性能优势。

无论如何，我相信你最好筛选包含所需权限的角色，然后仅为它们运行查询：

 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 r.role_id in (
                select role_id
                from   t_role_permission
                where  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

- David Aldridge

Answer 3

作为regexp_like路线的替代方案，您可以使用分析查询来实现此目的，特别是 listagg()的分析版本，并使用dense_rank()生成限制“行号”值：

select distinct role_id, role_name, active, companyName, permission_id,
    permission_name, rn, total_rows, roleCreated
from (
    select *
    from (
        select r.role_id, 
            r.role_name, 
            r.active, 
            decode(r.entity_type_id, 1000, m.name, 3000, cour.name,
                4000, 'Ensenda') companyName,
            p.permission_id as raw_permission_id,
            listagg(p.permission_id, ' | ')
                within group (order by p.permission_id)
                    over (partition by r.role_id) permission_id, 
            listagg(p.permission_name, ' | ')
                within group (order by p.permission_id)
                    over (partition by r.role_id) permission_name, 
            dense_rank() over (order by r.created_ts desc) as rn,
            count(distinct r.role_id) 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 raw_permission_id = 301446
)
where rn between 1 and 100
order by roleCreated desc;

如果您单独运行内部查询，您将看到每个role_id的多个结果; 每个结果都将包含listagg组件，但也会包括（暂时）各个permission_id值，我已将其别名为raw_permission_id。

然后，下一个查询可以过滤您感兴趣的确切权限。对于单个权限，就像您在这里一样，每个角色最多只会有一行，但是如果您正在寻找一系列权限中的一个，则可能会出现重复项（如果一个角色匹配了多个权限）。因此，外部查询排除了raw_permission_id并添加了distinct以抑制任何重复项。

- Alex Poole