这种需求如果按这种设计数据量大的话, 效率一定是大问题
SQL> select * from t;
ID SEL
---------- ------------------------
1 [1]1~[2]2~[3]1~[4]2~[5]1
1 [6]1~[7]2~[8]1~[9]1
1 [1]1~[2]2~[3]1~[4]2~[5]1
1 [6]1~[7]2~[8]1~[9]2
2 [6]1~[7]2~[8]1~[9]1
2 [1]1~[2]2~[3]1~[4]2~[5]1
6 rows selected.
SQL>
SQL> select id,
2 substr(flag, 1, instr(flag, ']')) shop_id,
3 sum(decode(substr(flag, instr(flag, ']') + 1), 1, 1, 0)) goods_1_cnt,
4 sum(decode(substr(flag, instr(flag, ']') + 1), 2, 1, 0)) goods_2_cnt
5 from
6 (select a1.id,
7 a1.sel,
8 substr('~' || a1.sel || '~',
9 instr('~' || a1.sel || '~', '~', 1, b1.rn) + 1,
10 instr('~' || a1.sel || '~', '~', 1, b1.rn + 1) - instr('~' || a1.sel || '~', '~', 1, b1.rn) - 1) flag
11 from
12 t a1,
13 (select level rn from (select max(length(sel) - length(replace(sel, '~', '')) + 1) len from t) connect by level <= len) b1
14 where length(a1.sel) - length(replace(a1.sel, '~', '')) + 1 >= b1.rn)
15 group by id, substr(flag, 1, instr(flag, ']'))
16 order by 1, 2;
ID SHOP_ID GOODS_1_CNT GOODS_2_CNT
---------- -------------------------------------------------------------------------------------------------------- ----------- -----------
1 [1] 2 0
1 [2] 0 2
1 [3] 2 0
1 [4] 0 2
1 [5] 2 0
1 [6] 2 0
1 [7] 0 2
1 [8] 2 0
1 [9] 1 1
2 [1] 1 0
2 [2] 0 1
2 [3] 1 0
2 [4] 0 1
2 [5] 1 0
2 [6] 1 0
2 [7] 0 1
2 [8] 1 0
2 [9] 1 0
18 rows selected.
SQL>