我有两张表想写一个视图合成一张表算出总分,结构如下。学生表
id name result_postion study_type
1 张三 1 1
2 张三 2 1
3 张三 3 1
4 李四 1 2
5 李四 2 2
6 李四 3 2
7 张三 1 3注:
name varchar(50) 姓名
result_postion int 第几次模拟考试成绩
study_type int 学科编号学生成绩表
id result_postion study_type result
1 1 1 60
2 2 1 70
3 3 1 80
4 1 2 40
5 2 2 50
6 3 2 60
7 1 3 100注:
result_postion int 第几次模拟
study_type int 学科编号
result int 成绩我想汇合成一张表,把某一个学生的几次模拟考试成绩汇总算出来。万望各位大神赐教!!
id name result_postion study_type
1 张三 1 1
2 张三 2 1
3 张三 3 1
4 李四 1 2
5 李四 2 2
6 李四 3 2
7 张三 1 3注:
name varchar(50) 姓名
result_postion int 第几次模拟考试成绩
study_type int 学科编号学生成绩表
id result_postion study_type result
1 1 1 60
2 2 1 70
3 3 1 80
4 1 2 40
5 2 2 50
6 3 2 60
7 1 3 100注:
result_postion int 第几次模拟
study_type int 学科编号
result int 成绩我想汇合成一张表,把某一个学生的几次模拟考试成绩汇总算出来。万望各位大神赐教!!
select 1 as s_id, '张三' as s_name, 1 as result_postion, 1 as study_type from dual union all
select 2 as s_id, '张三' as s_name, 2 as result_postion, 1 as study_type from dual union all
select 3 as s_id, '张三' as s_name, 3 as result_postion, 1 as study_type from dual union all
select 4 as s_id, '李四' as s_name, 1 as result_postion, 2 as study_type from dual union all
select 5 as s_id, '李四' as s_name, 2 as result_postion, 2 as study_type from dual union all
select 6 as s_id, '李四' as s_name, 3 as result_postion, 2 as study_type from dual union all
select 7 as s_id, '张三' as s_name, 1 as result_postion, 3 as study_type from dual ),
tmp2 as(
select 1 as p_id, 1 as result_postion, 1 as study_type, 60 as result from dual union all
select 2 as p_id, 2 as result_postion, 1 as study_type, 70 as result from dual union all
select 3 as p_id, 3 as result_postion, 1 as study_type, 80 as result from dual union all
select 4 as p_id, 1 as result_postion, 2 as study_type, 40 as result from dual union all
select 5 as p_id, 2 as result_postion, 2 as study_type, 50 as result from dual union all
select 6 as p_id, 3 as result_postion, 2 as study_type, 60 as result from dual union all
select 7 as p_id, 1 as result_postion, 3 as study_type, 100 as result from dual )
select tmp1.主键id, tmp2.result_postion, sum(tmp2.result) from tmp1 inner join tmp2 on tmp1.主键id = tmp2.学生表id group by tmp1.主键id, tmp2.result_postion由于不明白你的需求,而你所描述的内容及示例数据完全是没有经过思考的,无法给出答案,只能给一个参考例子。