如何在oracle中进行拆分和左联接 [英] How to do split and left join in oracle

问题描述

我与toad for oracle一起工作，并且我有一个具有多个关联的数据库(1-n). 我想将表中的varchar列(示例:1、2、3)与另一个表的int(id)(示例:1)列相关联.我正在数据库中为此操作创建一个视图，但是外部套用和交叉套用不起作用.

I work with toad for oracle and I have a database with multiple associations(1-n). I want to associate the varchar column (example :1,2,3) in table with the int (id)(example : 1) column of the other table . I am creating a view for this operation in database, but the outer apply and cross apply do not work.

在sql中找到的dbo.split命令中也不起作用.

Also did not work in the dbo.split command found in sql.

怎么办?

SELECT
      a.ID AS ID,
      a.ADI_NUMARASI AS aNAME,
      c.ID AS CODEID,   
      c.VALUE AS cVALUE   
 FROM  CUSTOMER a OUTER APPLY [dbo].[Split](a.TypeIDs, ',') Types 
LEFT JOIN CODE c ON c.ID= Types.Item  AND c.DOMAINID = a.DOMAINID

示例表

table CUSTOMER                                     table CODE
 ID | ADI_NUMARASI | TYPEIDS |DOMAINID         ID | DOMAINID | VALUE
 1      TEST1        1,2,3     1                1      1        XXX
                                                2      1        YYY
                                                3      2        ZZZ
                                                3      1        KKK

预期结果

 ID | aNAME | CODEID | cVALUE
 1    TEST1      1       XXX
 1    TEST1      2       YYY
 1    TEST1      3       KKK

推荐答案

尝试:

SELECT cu.ID , cu.ADI_NUMARASI , co.ID , co.VALUE
FROM 
(
  SELECT id,ADI_NUMARASI,TYPEIDS,DOMAINID,
  regexp_substr(TYPEIDS,'[^,]+', 1, level) typeidsnew 
  FROM Customer 
  GROUP BY id,ADI_NUMARASI,TYPEIDS,DOMAINID,
  regexp_substr(TYPEIDS, '[^,]+', 1, level)
  connect by regexp_substr(TYPEIDS, '[^,]+', 1, level) is not null
) cu 
LEFT JOIN Code co ON cu.typeidsnew = co.ID
AND cu.DomainId = co.domainId 
ORDER BY cu.ID,co.ID

输出: http://sqlfiddle.com/#!4/177e8/1

这篇关于如何在oracle中进行拆分和左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！