Oracle 替换功能

2022-01-25 00:00:00 sql replace compare mysql oracle

选择查询时，我需要用 Table2 的值替换 Table1 的字段值.

I need to replace the Table1's filed values from Table2's values while select query.

例如:

表1:

Org                  Permission
--------------------------------------
Company1             1,3,7
Company2             1,3,8

表2:

Permission          Permission
--------------------------------------
1                   Read
3                   Write
7                   Execute
8                   Delete

我需要这样的:

Org                  Permission
--------------------------------------
Company1             Read,Write,Execute
Company2             Read,Write,Delete

推荐答案

如果您不想更新现有表而只想选择数据，那么您可以使用这个有点费力的查询.

If you don't want to update the existing table and only want to select the data then you can use this somewhat laborious query.

http://sqlfiddle.com/#!4/22909/4

WITH changed_table AS
     (SELECT val1, EXTRACTVALUE (x.COLUMN_VALUE, 'e') val2new
        FROM (SELECT val1, val2 xml_str
                FROM table1),
             TABLE (XMLSEQUENCE (XMLTYPE (   '<e><e>'
                                          || REPLACE (xml_str, ',', '</e><e>')
                                          || '</e></e>'
                                         ).EXTRACT ('e/e')
                                )
                   ) x)
SELECT ct.val1, listagg(table2.val2,',') within group (order by table2.val2) val2
  FROM changed_table ct, table2 table2
 WHERE ct.val2new = table2.val1
group by ct.val1;

我使用 XMLTYPE 将逗号分隔的数字分隔为行.然后将行与第二个表连接以获得描述，最后使用 LISTAGG 函数形成逗号分隔的字符串.不知道这个查询效率如何.我同意马克班尼斯特的评论.

I have used the XMLTYPE to separate the comma separated numbers to rows. Then joined the rows with second table to get the description and finally used the LISTAGG function to form comma separated string. Don't know how efficient this query is. I agree with Mark Bannister's comment.

相关文章