Oracle SQL创建虚拟列，执行最大操作，然后计数和显示记录。

Question

对不起，如果标题有点误导，但我不知道怎么说。不管怎样，回到手头的问题上。我有两个表WSM(包含仓库id、名称和与它相关的风险)和SUTSM_REP(存储和仓库关系)。下面是它们所持有的值：WSM表

代表

每个仓库都有与其相关的风险(4个风险类别--低风险、中等风险、高风险和临界风险)，一个商店从多个仓库获取货物。如果一家商店从3个仓库(比如w1(中等风险)、w2(高)、w3(低))得到东西，那么与商店相关的风险是‘高’的。类似地，如果一家商店从两个仓库得到东西--一个有中等风险，另一个有低风险，那么商店就会有“中等”风险。我希望我明白这一点。我被要求把每一种风险都记在商店的数量上。所以我创建了另一个有风险和风险的表。我分配1到低，2到中等，3到高，4到临界。然后，我加入了这3张表，同时加入了最大风险指数( risk id )，然后对此进行了统计。以下是我的查询：

select RID, count(sp_id)
from
  (select a.sp_id, max(c.r_id) AS RID
   from SUTSM_REP a,
        WSM b,
        wsm_rid c
   where a.sm_id = b.sm_id and b.smr = c.smr
   group by a.sp_id) t
group by RID;

结果是:我的查询结果

但是老板不想创建第三个表，他想要的是风险名(低、中、高、关键)，而不是风险ID。因此，我将查询修改为：

with t as (
  select sm_id, sm_name, smr, DECODE(smr, 'Low', 1,
                                    'Medium', 2,
                                    'High', 3,
                                    'Critical', 4,
                                    0) RISK_ID 
   from wsm )
select rid, count(sp_id) from 
    ( select sp_id, max(risk_id) rid 
      from (select a.sp_id, b.sm_id, b.risk_id 
            from SUTSM_REP a, t b 
            where a.sm_id=b.sm_id) 
       group by sp_id ) 
group by rid;

我使用wsm创建了一个临时表，并将风险id添加到风险类别/级别。然后使用了和前面相同的逻辑。此查询的输出是：

我们使用Oracle SQL。有人能帮我或指点我如何获得风险类别/级别的名称吗？我在这里束手无策。提前感谢任何海报的帮助。

Answer 1

最好有一个风险表，列明风险名称及其相关性。如果不允许您创建这样的表，请将其创建为具有动态WITH的临时视图。

下一步是获得最高的风险每家商店，这可以通过加入上面的风险表和KEEP LAST。

最后一步是计算每个风险的商店。

with risks as
(
  select 'Low' as smr, 1 as relevance from dual
  union all
  select 'Medium' as smr, 2 as relevance from dual
  union all
  select 'High' as smr, 3 as relevance from dual
  union all
  select 'Critical' as smr, 4 as relevance from dual
)
, store_risks as
(
  select
    sw.sp_id,
    max(w.smr) keep (dense_rank last order by r.relevance) as risk,
    max(r.relevance) as risk_sortkey
  from sutsm_rep sw -- the store/warehouse bridge
  join wsm w on w.sm_id = sw.sm_id -- the warehouses
  join risks r on r.smr = w.smr -- the risks
  group by sw.sp_id
)
select risk, count(*)
from store_risks
group by risk
order by max(risk_sortkey);