-- 字段含有逗号分隔的,如何把这条记录按分隔符分成多条记录;
CREATE TABLE T_TST_INFO
(
   PERSON_ID NUMBER,
   PERSON_NAME VARCHAR(20),
   TYPE_ID VARCHAR(200)
);


INSERT INTO T_TST_INFO
  SELECT 1, '张三', '1,2' FROM DUAL
  UNION ALL
  SELECT 2, '李四', '1' FROM DUAL 

SELECT * FROM T_TST_INFO  


SELECT PERSON_ID,
       PERSON_NAME,
       REGEXP_SUBSTR(TYPE_ID, '[^,]+', 1, LEVEL) TYPE_ID
  FROM T_TST_INFO
CONNECT BY LEVEL <= REGEXP_COUNT(TYPE_ID, ',') + 1
       AND PERSON_ID = PRIOR PERSON_ID
       AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;  

-- AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
-- 避免CONNECT BY 递归循环;       
       

SELECT DISTINCT PERSON_ID,
                PERSON_NAME,
                LEVEL,
                REGEXP_SUBSTR(TYPE_ID, '[^,]+', 1, LEVEL) TYPE_ID
  FROM T_TST_INFO
CONNECT BY LEVEL <= REGEXP_COUNT(TYPE_ID, ',') + 1;

-- 0201.单科明细表客观题计算;

-- 原始表
/*SELECT * FROM T_SCN_RCG_OMR
WHERE T_SCN_RCG_OMR.EXAM_COURSE_ID = '3208075F3F8A4BD19032B41331A1C133'
AND T_SCN_RCG_OMR.TEST_NO = '1610603';*/


-- 得到5列:EXAM_COURSE_ID,TEST_NO,QUESTIONS_ID,QUESTIONS_NAME,ANSWERS

SELECT 
    M1.EXAM_COURSE_ID
    ,M1.TEST_NO
    ,M2.QUESTIONS_ID
    ,M2.QUESTIONS_NAME
    ,M2.ANSWERS
   FROM (
SELECT T_SCN_RCG_OMR.TEST_NO,
       T_SCN_RCG_OMR.EXAM_COURSE_ID,
       LEVEL ROW_INDEX,
       REGEXP_SUBSTR(SCORE_DETAIL, '[^,]+', 1, LEVEL) SCORE_DETAIL
  FROM T_SCN_RCG_OMR
    WHERE T_SCN_RCG_OMR.TEST_NO = '1610615'
CONNECT BY LEVEL <= REGEXP_COUNT(SCORE_DETAIL, ',') + 1
       AND TEST_NO = PRIOR TEST_NO
       AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL ) M1 
       
LEFT JOIN (

SELECT 
      T2.EXAM_COURSE_ID
      ,T2.QUESTIONS_ID
      ,T2.QUESTIONS_NAME
      ,T2.SERIAL_NO
      ,T2.ANSWERS
      ,ROW_NUMBER() OVER(PARTITION BY EXAM_COURSE_ID ORDER BY SERIAL_NO ASC) ROW_INDEX 
 FROM T_TPL_OBJECTIVE_QUESTIONS T2 
WHERE T2.EXAM_COURSE_ID = '3208075F3F8A4BD19032B41331A1C133' ) M2 
   ON M1.EXAM_COURSE_ID = M2.EXAM_COURSE_ID AND M1.ROW_INDEX = M2.ROW_INDEX