基于另一个表在表中插入值

Question

我需要编写一个存储过程。 我有一个表的一些数据现在我需要根据一些条件相关数据插入到另一个表

实施例：

Table 1 

Name  Class Math Physics English 
Alok  V  60 50  45 
Bobby V  78 87  86 
Chandini VI 56 76  56 
Dolly VII 87 56  66 

基于此条件

Insert Values into Table2 
If class =V 
(Table2.Physics=Select sum(Table1.Physics) from Table1 where Class like ‘V’ 
Table2.Maths=0 and table2.English=0) 

If class =VI 
(Table2.Maths=Select sum(Table1.Maths) from Table1 where Class like ‘VI’ 
Table2.Physics=0 and table2.English=0) 

If class =VI 
(Table2.English =Select sum(Table1.English) from Table1 where Class like ‘VII’ 
Table2.Physics=0 and table2.Maths =0) 
] 

 

Table 2 

Class Math Physics English 
V  0 137  0 
VI 56 0  0 
VII 0 0  66 

请帮我解决

Answer 1

INSERT 
INTO table2 
SELECT class, 
     SUM(CASE WHEN class = 'V' THEN Physics ELSE 0 END), 
     SUM(CASE WHEN class = 'VI' THEN Math ELSE 0 END), 
     SUM(CASE WHEN class = 'VII' THEN English ELSE 0 END) 
FROM table1 
GROUP BY 
     class 

Answer 2

您是否必须使用存储过程或甚至另一个表？

A 查看服务器更好，因为它永远不会与真实数据同步。 （也没有真正的性能问题。）

CREATE VIEW table2 AS 
SELECT class, 
     SUM(CASE WHEN table1.class = 'V' THEN table1.physics ELSE 0 END), 
     SUM(CASE WHEN table1.class = 'VI' THEN table1.math ELSE 0 END), 
     SUM(CASE WHEN table1.class = 'VII' THEN table1.english ELSE 0 END) 
FROM table1 
GROUP BY class