在基于另一个表的表中插入值 [英] Insert values in a table based on another table

问题描述

我需要编写一个存储过程.
我有一个包含一些数据的表，现在我需要根据某些条件将相关数据插入到另一个表中

示例:
表1

Hi,

I need to write a Stored Procedure.
I have a table with some data now I need to insert relevant data into another table according to some condition

Example:
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

[根据这种情况
将值插入到Table2中

[Based on this condition
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)
]

表2

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

请帮助我

Kindly Help me out

推荐答案

我曾经为这类问题提供代码.然后我意识到，我没有帮上忙，因为我正在喂正在问的那个人.无论如何，您可以做的是用选择插入.例如

I used to give codes to these kinds of question. Then I realized, I am not helping out because I am spoonfeeding the one who is asking. Anyway, what you can do is to do an insert with a select. For example

INSERT INTO TABLE2(<FIELDS>) SELECT <FIELDS> FROM TABLE1 WHERE... 

尝试一下，如果遇到困难，请回来提出问题.

Try that one out and if you get stuck, come back and ask questions.

这可以通过多种方式完成，但是您是否尝试过类似的操作(根据您的描述进行翻译):

This can be done in many ways but have you tried for example something like (translated from your description):

INSERT INTO Table2 (Class, Math, Physics, ...)
SELECT Class,
       CASE
          WHEN Class = 'VI' THEN (SELECT SUM(t1.Math) FROM Table1 t1 WHERE t1.Class = t.Class)
          ELSE 0
       END,
       CASE
          WHEN Class = 'V' THEN (SELECT SUM(t1.Physics) FROM Table1 t1 WHERE t1.Class = t.Class)
          ELSE 0
       END,
       ...
FROM Table1 t

这篇关于在基于另一个表的表中插入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！