sql - 用表中的数据替换硬编码的值

Question

目前，我有3个从属关系在查询中进行了硬编码。它们充当继承关系:1 = Faculty, 2 = Staff, 3 = Student。如果affiliations_tbl表中的用户具有多个从属关系(例如:也是学生的职员)，则它将使用其职员从属关系，因为它在partition by和decode()定义的层次结构中较高。

SELECT x2.emplid,
       scc_afl_code
  FROM (SELECT x.emplid,
               scc_afl_code,
               row_number() over(partition BY x.emplid ORDER BY x.affil_order) r
          FROM (SELECT t.emplid,
                       scc_afl_code,
                       DECODE(scc_afl_code,
                              'FACULTY',
                              1,
                              'STAFF',
                              2,
                              'STUDENT',
                              3,
                              999) affil_order
                  FROM affiliations_tbl t
                 WHERE t.scc_afl_code IN
                       (SELECT a.scc_afl_code
                          FROM affiliation_groups_tbl a
                         WHERE a.group = 'COLLEGE')) x) x2
 WHERE x2.r = 1;

我创建了一个表，该表将存储从属组 affiliation_groups_tbl，因此我可以通过向表中添加数据来扩展此表，而不用更改此查询中的硬编码值。示例:我将其添加到表中，而不是将 'CONSULTANT', 4添加到 decode()列表中，因此不必修改SQL。

scc_afl_code |  group  | group_name | sort_order 
-------------+---------+------------+-----------
FACULTY      | COLLEGE | Faculty    |     1
STAFF        | COLLEGE | Staff      |     2
STUDENT      | COLLEGE | Student    |     3

我已经更新了查询的后半部分，以仅选择 scc_afl_code组中的 COLLEGE_GROUP。 如何正确更新查询的第一部分以将表用作层次结构？

Answer 1

请尝试以下代码，而不是在语句的select子句中进行解码:

coalesce((
    select g.sort_order 
    from affiliation_groups_tbl g
    where g.scc_afl_code = t.scc_afl_code ), 999)