逆分组计算

【问题】

I need a list of user IDs (course_user_ids) that is currently stored in a single field of a larger table.

I have a table calledcoursesthat contains course information withcourse_idandcourse_studentsas such:

-----------------------------------------------------------
| course_id | course_students |
----------------------------------------------------------
| 1 | a:3:{i:0;i:12345;i:1;i:22345;i:2;i:323456;} |
-----------------------------------------------------------
| 2 | a:32:{…} |
-----------------------------------------------------------

Thecourse_studentspart contains 3 chunks of information:

1. the number of students (a:3:{…) -- not needed

2. the order/key for the array of each student ({i:0;… i:1;… i:2; …}) -- also not needed

3. thecourse_user_id(i:12345; … i:22345;… i:32345;)

I only need thecourse_user_idand the originalcourse_id, resulting in a new table that i can use for joins/subqueries like this:

------------------------------
| course_id | course_user_id |
------------------------------
| 1 | 12345 |
------------------------------
| 1 | 22345 |
------------------------------
| 1 | 323456 |
------------------------------

(ideally able to continue to break out values for othercourse_ids andcourse_user_ids, but not a priority:)

| … | … |
------------------------------
| 2 | … |
------------------------------
| 2 | … |
------------------------------
| 97 | … |
------------------------------
| 97 | … |
------------------------------
| … | … |
------------------------------

Note: thecourse_user_idcan vary in length (some are 5 digits, some are 6)

Any ideas would be much appreciated!

有人给出解答，但楼主不满意

select c.course_id,u.user_id

from

courses c

join users u

on u.user_id=if(instr(c.course_students,concat(“:”,u.user_id,“;”))>0,u.user_id,c.course_students)

【回答】

需要先解析出括号里的字符串，再按偶数位置取数，之后根据集合生成多条记录。SQL 做这种逆分组很难写，可用 SPL 实现，代码如下：

	A
1	$select course_id,course_students from tb
2	=A1.run(course_students=course_students.split@b(";").step(2,2).(~.split(":")(2)))
3	=A2.news(course_students;A2.course_id,~:course_user_id)

A2：从 course_students 列中拆出子串集合。函数 split 可将字符串拆为集合，step(2,2) 表示取偶数位置的集合成员。

A3：生成新序表

脚本写好后，在其他应用程序中，就可以利用集算器 JDBC 调用该数据集了。Java 如何调用 SPL 脚本