sql - 如何在两个表之间的组中实现递归连接？

Question

我在一个名为 AVAILABLE_TEMPLATES 的表中有一些杂乱的数据。 .这是一个简单的例子:

TEMPLATE_GROUP TEMPLATE_NAME LOCALE
-------------- ------------- ------
RO              LTRU          fi_FI  
RO              LTRU          se_SE  
RO              LTRU          en_US  
BL              V1PRO         se_SE  
BL              V1PRO         en_US  

我有另一个表，其中包含名为 SYSTEM_LOCALES 的语言环境。 .

SYS_LOCALE
------
lv_LV  
fi_FI  
sv_SE  
en_US

我希望通过连接这两个表获得的数据应该是 SYS_LOCALE 中行的笛卡尔积。表和不同的TEMPLATE_GROUP/TEMPLATE_NAME来自 AVAILABLE_TEMPLATES表。

我们的默认语言环境是 fi_FI .对于每个 TEMPLATE_NAME在 TEMPLATE_GROUP , 我想检查一个匹配的语言环境是否可用，如果是，它应该返回为 USE_LOCALE .如果找不到匹配的语言环境，我想返回系统的默认语言环境，即 fi_FI如果它存在于 TEMPLATE_NAME和 TEMPLATE_GROUP作为 USE_LOCALE .

以下是连接两个表应返回的内容:

TEMPLATE_GROUP TEMPLATE_NAME SYS_LOCALE  USE_LOCALE
-----------    ------------- ------      ----------
RO             LTRU          lv_LV       fi_FI      --There's a fi_FI locale but no lv_LV 
RO             LTRU          fi_FI       fi_FI    
RO             LTRU          se_SE       se_SE    
RO             LTRU          en_US       en_US    
BL             V1PRO         lv_LV       NULL       --There's no lv_LV or a fi_FI locale 
BL             V1PRO         fi_FI       NULL       --There's no fi_FI locale
BL             V1PRO         se_SE       se_SE    
BL             V1PRO         en_US       en_US    

我一直无法弄清楚这一点，并且对此一无所知。是否必须通过递归来完成？谢谢

Answer 1

唯一允许您在不多次读取表格的情况下执行此操作的结构是partitioned outer join。 .

以下是您的数据集的示例:

SQL> create table available_templates (template_group,template_name,locale)
  2  as
  3  select 'RO', 'LTRU', 'fi_FI' from dual union all
  4  select 'RO', 'LTRU', 'se_SE' from dual union all
  5  select 'RO', 'LTRU', 'en_US' from dual union all
  6  select 'BL', 'V1PRO', 'se_SE' from dual union all
  7  select 'BL', 'V1PRO', 'en_US' from dual
  8  /

Table created.

SQL> create table system_locales (sys_locale)
  2  as
  3  select 'lv_LV' from dual union all
  4  select 'fi_FI' from dual union all
  5  select 'se_SE' from dual union all
  6  select 'en_US' from dual
  7  /

Table created.

以及分区外连接:

SQL> select at.template_group
  2       , at.template_name
  3       , sl.sys_locale
  4       , nvl
  5         ( at.locale
  6         , max(decode(at.locale,'fi_FI',at.locale)) over (partition by at.template_group, at.template_name)
  7         ) use_locale
  8    from system_locales sl
  9         left outer join available_templates at
 10           partition by (at.template_group,at.template_name)
 11           on (at.locale = sl.sys_locale)
 12  /

TEMPLATE_GROUP TEMPLATE_NAME SYS_LOCALE USE_LOCALE
-------------- ------------- ---------- ----------
BL             V1PRO         en_US      en_US
BL             V1PRO         fi_FI
BL             V1PRO         lv_LV
BL             V1PRO         se_SE      se_SE
RO             LTRU          en_US      en_US
RO             LTRU          fi_FI      fi_FI
RO             LTRU          lv_LV      fi_FI
RO             LTRU          se_SE      se_SE

8 rows selected.

这是表只被扫描一次的证据:

    SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'))
      2  /

    PLAN_TABLE_OUTPUT
    ---------------------------------------------------------------------------------------------------------------------------------------
    SQL_ID  57br33gc6n1sc, child number 0
    -------------------------------------
    select at.template_group      , at.template_name      , sl.sys_locale
       , nvl        ( at.locale        ,
    max(decode(at.locale,'fi_FI',at.locale)) over (partition by
    at.template_group, at.template_name)        ) use_locale   from
    system_locales sl        left outer join available_templates at
     partition by (at.template_group,at.template_name)          on
    (at.locale = sl.sys_locale)

    Plan hash value: 921719364

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |      1 |        |      8 |00:00:00.01 |       6 |       |       |          |
|   1 |  WINDOW BUFFER               |                     |      1 |      1 |      8 |00:00:00.01 |       6 |  2048 |  2048 | 2048  (0)|
|   2 |   VIEW                       |                     |      1 |      1 |      8 |00:00:00.01 |       6 |       |       |          |
|   3 |    MERGE JOIN PARTITION OUTER|                     |      1 |      1 |      8 |00:00:00.01 |       6 |       |       |          |
|   4 |     SORT JOIN                |                     |      3 |      4 |      9 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   5 |      TABLE ACCESS FULL       | SYSTEM_LOCALES      |      1 |      4 |      4 |00:00:00.01 |       3 |       |       |          |
|*  6 |     SORT PARTITION JOIN      |                     |      9 |      5 |      5 |00:00:00.01 |       3 |  2048 |  2048 | 2048  (0)|
|   7 |      TABLE ACCESS FULL       | AVAILABLE_TEMPLATES |      1 |      5 |      5 |00:00:00.01 |       3 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("AT"."LOCALE"="SL"."SYS_LOCALE")
       filter("AT"."LOCALE"="SL"."SYS_LOCALE")

Note
-----
   - dynamic sampling used for this statement (level=2)


35 rows selected.

问候，
抢。