Oracle拆分合并行
Oracle拆分合并行
多列合并成一行：
STORE_NO APPLY_NOATTACH_NAMECREATE_DATE
12 20130828000345 acc1ae055a1d47bdba8069420b08bf13.jpg 2013-08-28 4:09:30
06 20130828000345 61e815edf3ba4cbd92b0c5eca53e1a4f.gif 2013-08-28 4:07:55
12 20130828000345 87544906afd44cd8b2a202a761c599fe.gif 2013-08-28 4:09:20
06 20130828000345 9df3c67bdc7d4d1f949ef096e389988d.gif 2013-08-28 3:58:33
[sql]
SELECT STORE_NO,
MAX(LTRIM(SYS_CONNECT_BY_PATH(ATTACH_NAME, ','), ',')) ATTACH_NAME
FROM (SELECT STORE_NO,
APPLY_NO,
ATTACH_NAME,
ROW_NUMBER() OVER(PARTITION BY STORE_NO ORDER BY CREATE_DATE DESC) RN
FROM _ATT
WHERE APPLY_NO = '20130828000345')
START WITH RN = 1
CONNECT BY PRIOR RN + 1 = RN
GROUP BY STORE_NO
STORE_NO ATTACH_NAME
1 151401012 acc1ae055a1d47bdba8069420b08bf13.jpg,87544906afd44cd8b2a202a761c599fe.gif
2 151401006 acc1ae055a1d47bdba8069420b08bf13.jpg,9df3c67bdc7d4d1f949ef096e389988d.gif
有时候处理多对多关系的时候我们可能会对该关联关系仅是以一列来处理，如下：
[sql]
SELECT *
FROM (SELECT REGEXP_SUBSTR(BRAND_JOIN, '[^|]+', 1, ROWNUM) BRAND
FROM (SELECT '11|12|13|14|15|-16|16|17|18|19|20|21|22|23|24|25|26|27|28|29|-31|30|31|32|36|39|44|45|46|47|48' BRAND_JOIN
FROM DUAL)
CONNECT BY ROWNUM <= LENGTH(REGEXP_REPLACE(BRAND_JOIN, '|', '')))
WHERE BRAND IS NOT NULL
BRAND
11
12
13
14
15
-16
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-31
30
31
32
36
39
44
45
46
47
48
该列就变成一个结果集了，方便使用in();
既然多列可以合并，那肯定也是可以拆分的，根据如上合并做以下拆分:
[sql]
SELECT DECODE(B, 0, SUBSTR(BRAND_JOIN, C), SUBSTR(BRAND_JOIN, C, B - C))
FROM (SELECT BRAND_JOIN, B, (LAG(B, 1, 0) OVER(ORDER BY LV)) + 1 C
FROM (SELECT BRAND_JOIN, INSTR(BRAND_JOIN, '|', 1, LEVEL) B, LEVEL LV
FROM (
SELECT '11|12|13|14|15|-16|16|17|18|19|20|21|22|23|24|25|26|27|28|29|-31|30|31|32|36|39|44|45|46|47|48' BRAND_JOIN FROM dual
)
CONNECT BY LEVEL <=LENGTH(regexp_REPLACE(BRAND_JOIN, '[^|]', ''))+1))
效果也是一样的
