当一个表中没有值时，将两个表连接起来 [英] Join two tables when there is no value in one table

问题描述

我有两个表，ROLE和ROLE_DESC.

I have two tables, ROLE and ROLE_DESC.

角色看起来像这样.

PS_ROLE_SEQ  NUMBER,    
PS_SEQ       NUMBER,    
ROLE_TYPE            VARCHAR2(2 CHAR),    
SOURCE               VARCHAR2(128 CHAR)

ROLE_DESC看起来像这样.

ROLE_DESC looks like this.

ROLE_TYPE  VARCHAR2(2 CHAR),    
ROLE_NAME  VARCHAR2(16 CHAR)

如您所料，ROLE的ROLE_TYPE和ROLE_DESC的ROLE_TYPE互相引用. 像这样，ROLE_DESC中有三个实体.

As you expect, ROLE_TYPE of ROLE and ROLE_TYPE of ROLE_DESC reference each other. There are three entities in ROLE_DESC, like this.

ROLE_TYPE   ROLE_NAME
A           Account
M           Manager
S           Sales

我想使用where子句将此字符串用于特定的PS_SEQ.

I would like to make this string for a particular PS_SEQ using where clause.

(Account + ":" + ROLE.SOURCE + "|" + Manager + ":" + ROLE.SOURCE + "|" + Sales + ":" + ROLE.SOURCE)

有时，ROLE表没有完整的ROLE_TYPE，例如，它只有Account和Manager. 在这种情况下，Sales的ROLE.SOURCE变成". 而且，ROLE_DESC是动态的，可以更改实体的数量.

Sometimes, ROLE table does not have whole ROLE_TYPE, for example, it only has Account and Manager. In that case, ROLE.SOURCE of Sales become just "". And, ROLE_DESC is dynamic, the number of entities can be changed.

有什么办法可以方便地查询到这个吗?

Is there any way I can query this easily?

推荐答案

SELECT LISTAGG (role_name || ':' || source, '|') WITHIN GROUP (ORDER BY role_name)
  FROM (SELECT ROLE_DESC.ROLE_TYPE, ROLE_DESC.ROLE_NAME, ROLE.SOURCE
          FROM ROLE_DESC 
           LEFT OUTER JOIN ROLE 
           ON ROLE_DESC.role_type = ROLE.role_type AND ROLE.PS_SEQ = 111)

这篇关于当一个表中没有值时，将两个表连接起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！