主页 / dba / 问题 / 40290
Accepted
maple_shaft
Asked: 2013-04-18 15:24:26 +0800 CST

Oracle SQL，如何按等级过滤集合？

  • 772

我现在正在努力处理以下 SQL 查询：

http://sqlfiddle.com/#!4/736a7/12

select uc.*
from usercredential uc
inner join users u on u.userid = uc.userid
inner join credential c on c.credentialid = uc.credentialid
inner join healthsystemcredential hsc on hsc.credentialid = c.credentialid
inner join accesslevel ac on hsc.hscredentialid in (
  -- Trying to figure out how to filter here?
  select 1 from dual
)
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null) or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) )

基本上，我试图获得与凭据多对一的 UserCredential。凭证与 HealthSystemCredential 是一对多的。

HealthSystemCredential 具有 AccessLevel 的外键，但是 HealthSystemCredential 中的此 AccessLevel 列实际上是最低允许访问级别。AccessLevel 记录的类型等级介于最低的 1 和最高的 4 之间。

示例：当我按类型为 2、3 或 4 的 AccessLevels 过滤查询时，应获取具有 Type Rank 为 2 的 AccessLevel 记录的 HealthSystemCredential。

问题是我无法在一个查询中弄清楚如何做到这一点。有什么方法可以让我在 AccessLevels 的子查询中获取 DENSE_RANK 列，在子查询中应用我的过滤器并将其以某种方式加入到表中，以提供我想要的所有适用的 HealthSystemCredentials？

oracle

2 个回答

  1. Best Answer

    实际上，您可以使用多种排名功能。您可以阅读 Oracle 文档中的分析函数。在你的情况下，如果我理解你的话，它会起作用RANK()：DENSE_RANK()

    select *
from
  ( select uc.*,
           DENSE_RANK() OVER (PARTITION BY ac.HEALTHSYSTEMID
                              ORDER BY ac.ACCESSLEVELTYPE ASC) 
             AS drnk
    from usercredential uc
      inner join users u 
              on u.userid = uc.userid
      inner join credential c 
              on c.credentialid = uc.credentialid
      inner join healthsystemcredential hsc 
              on hsc.credentialid = c.credentialid
      inner join accesslevel ac 
              on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID 
    where c.fulfillmentmethod = 'Attach'
    and c.display = 1
    and uc.auditstatus <> 0
    and ( (uc.auditstatus is null) 
       or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) 
        ) 
  ) tmp
where drnk = 1 ;

    当然，在应用内部的其他条件drnk = 1之后，将应用（最低类型等级）的条件where。如果您想首先过滤最低的类型等级（这可能会在最终结果中减少您的行数），您可以在派生表中分组，然后加入：

    select uc.*
from usercredential uc
  inner join users u 
          on u.userid = uc.userid
  inner join credential c 
          on c.credentialid = uc.credentialid
  inner join healthsystemcredential hsc 
          on hsc.credentialid = c.credentialid
  inner join ( SELECT HEALTHSYSTEMID,
                      MIN(ACCESSLEVELTYPE) AS ACCESSLEVELTYPE 
               FROM accesslevel 
               GROUP BY HEALTHSYSTEMID
             ) ac 
          on hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID 
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null) 
   or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) 
    ) ;

    或在ON子句中使用子查询。这更接近您的尝试，但相当复杂。SQL-ServerCROSS APPLY使这种连接更容易编写：

    select uc.*
from usercredential uc
  inner join users u 
          on u.userid = uc.userid
  inner join credential c 
          on c.credentialid = uc.credentialid
  inner join healthsystemcredential hsc 
          on hsc.credentialid = c.credentialid
  inner join accesslevel ac
          on ac.HEALTHSYSTEMID = hsc.HEALTHSYSTEMID
         and ac.ACCESSLEVELTYPE = 
             ( SELECT MIN(aci.ACCESSLEVELTYPE) AS ACCESSLEVELTYPE
               FROM accesslevel aci
               WHERE aci.HEALTHSYSTEMID = hsc.HEALTHSYSTEMID              
             )
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null) 
   or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) 
    ) ;

    在SQL-Fiddle试过。您可以使用表中的数据进行测试，以查看哪一个符合您的要求，以及连接是否应该在hsc.HEALTHSYSTEMID = ac.HEALTHSYSTEMID其他列上。

    • 3

  2. 我觉得 ypercube 有一个很好的答案来解释 DENSE_RANK 以及如何将其用于我的查询，所以我决定接受他的答案，但我想发布一个额外的答案，详细说明我最终做了什么。

    最终，我认为我能够在不使用分析函数的情况下完成此任务。这是我在下面提出的查询：

    with minimumaccessleveltype as (
  select ac_.accessleveltype as accessleveltype from vs.accesslevel ac_
  where ac_.accesslevelid = 3
)
select uc.*
from vs.usercredential uc
inner join vs.users u on u.userid = uc.userid
inner join vs.credential c on c.credentialid = uc.credentialid
inner join vs.healthsystemcredential hsc on hsc.credentialid = c.credentialid
where c.fulfillmentmethod = 'Attach'
and c.display = 1
and uc.auditstatus <> 0
and ( (uc.auditstatus is null) or (uc.auditstatus = 1 and uc.lastmodified > uc.auditdate) )
and uc.lastmodified > TO_DATE('01-APR-2013','DD-MON-YYYY')
and exists (select null
  from vs.accesslevel ac
  where ac.healthsystemid = hsc.healthsystemid
  and hsc.accesslevelid = ac.accesslevelid
  and ac.accessleveltype <= (select accessleveltype from minimumaccessleveltype)
  )
and hsc.healthsystemid = 3

    请注意，我的默认过滤器有示例值，这是为了证明查询实际上返回并过滤掉了我期望的数据。

    访问级别 ID 3：访问​​级别类型 = 6

    访问级别 ID 2：访问级别类型 = 4

    访问级别 ID 1：访问级别类型 = 1

    我有一个满足附加过滤的 UserCredential 记录，并指向 AccessLevel ID 为 2 的 Credential/HealthSystemCredential。当过滤 AccessLevel ID 为 3 和 2 时，我希望返回此记录。当过滤 AccessLevel ID 为 1 时，Type 太低，我希望它被过滤掉。

    上面的查询似乎通过了我的单元测试期望，所以我对这个解决方案很满意。解释计划成本低，速度快。该WITH子句将只返回 1 条记录，因此即使是针对此的 CARTESIAN 也不会影响查询。

    • 2

相关问题