多表联合行转列

【问题】

已知有三个表，学生表，成绩表，补考成绩表，表内容如下：

学生表
stu_id  stu_name  class_id
1             张三          1-1
2             李四          1-1
3             王五          1-2

成绩表
stu_id    subject   score   
1              java       77
1              c++        80
2              java       67
2              c++        58
3              java       56
3              c++        85

补考成绩表
stu_id    subject   score
2            c++          78
3            java          82

想实现查询三个表得到学生的成绩和补考成绩，结果如下：
stu_id      stu_name     java_score     c++_score     scores(总成绩)   java 补考成绩    c++ 补考成绩
1                 张三              77                      80                156                       
2                 李四              67                      58                125                                                   78
3                 王五              56                      85                 141                 82

求问这个 sql 语句怎么写？

【回答】

该问题属于动态行转列类的问题，SQL 很麻烦，要动态生成一条再执行，可以 SPL 来做，实现动态语法简单很多：

	A	B
1	$select t1.stu_id stu_id,t1.stu_name   stu_name,t2.subject subject,t2.score score1,t3.score score2 from学生表.txt t1 left join成绩表.txt t2 on  t1.stu_id=t2.stu_id left join补考成绩表.txt t3 on t1.stu_id=t3.stu_id   and t2.subject=t3.subject order by t1.stu_id,t2.subject
2	=A1.group(stu_id)
3	=A1.group(subject)
4	=create(stu_id,stu_name,${(A3.(~.subject+"_score")|"scores(总成绩)"|A3.(~.subject+"补考成绩")).concat@c()})
5	for A2	>A4.record([A5.stu_id,A5.stu_name]|A3.(~(#A5).score1)|A5.sum(score1)|A3.(~(#A5).score2))

A1：执行 SQL 关联 3 个表取数，要用 left join 确保学生表是完整的

A2：按 stu_id 分组

A3：按科目分组

A4：创建结果序表

A5-B5：循环 stu_id 分组，根据 subject 分组动态添加结果记录，A4 最终结果为：