我有兩張表想寫一個視圖合成一張表算出總分，結構如下。
學生表
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 成績
我想匯合成一張表，把某一個學生的幾次模擬考試成績匯總算出來。
萬望各位大神賜教！！
uj5u.com熱心網友回復：
連學生編號都沒與偶，算個錘子的總分啊uj5u.com熱心網友回復：
/*學生表 主鍵id, 學生編號，學生姓名，第幾次模擬考試（多余）, 學科（多余）*//*成績表 學生表id，第幾次模擬考試，學科，分數*/
with tmp1 as (
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
由于不明白你的需求，而你所描述的內容及示例資料完全是沒有經過思考的，無法給出答案，只能給一個參考例子。
uj5u.com熱心網友回復：
select a.name,a.result_postion,sum(b.result) as 每次考試總成績 from 學生表 a
join 學生成績表 b on a.result_postion=b.result_postion and a.study_type =b.study_type
group by a.name,a.result_postion
select a.name,sum(b.result) as 總成績 from 學生表 a
join 學生成績表 b on a.result_postion=b.result_postion and a.study_type =b.study_type
group by a.name
uj5u.com熱心網友回復：
-- 合成一張表select * from 學生表 a,成績表 b where a.result_postion=b.result_postion and a.study_type=b.study_type
-- 張三學生所有模擬考試的總成績
select sum(b.result) from 學生表 a,成績表 b where a.result_postion=b.result_postion and a.study_type=b.study_type where a.name='張三'
-- 張三學生第一次模擬考試的成績
select sum(b.result) from 學生表 a,成績表 b where a.result_postion=b.result_postion and a.study_type=b.study_type where a.name='張三' and a.result_postion =1
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/34985.html
標籤：MySQL
上一篇：vip權益資料庫怎么設計