Table1
Id name je1 je2 je3
1 aaa
2 BBB
2 CCC
Table2
Id xm_dm je
1 01 100
1 01 110
50
1.1 03 200
330
2.20
2.2 03 80
Can use an UPDATE implementation in the table table1 je1, je2, je3 data input. When the table1. Id=table2. Id and table2. Xm_dm=01, table1. Je1 is equal to the table2. Je accumulated, when the table1. Id=table2. Id and table2. Xm_dm=02, table1. Je2 is equal to the table2. Je accumulated, when the table1. Id=table2. Id and table2. Xm_dm=03, table1. Je3 is equal to the table2. Je accumulated. As a result for
Table1
Id name je1 je2 je3
1 aaa 210 50 200
2 350 80 BBB
2 CCC
Thank you very much!

CodePudding user response:

If not, can only use the trigger,

CodePudding user response:

 create table tb1 (int id, name varchar (10), je1 int, je2 int, je3 int) 
Insert into tb1 values (1, "aaa", 0, 0) 
Insert into tb1 values (2, 'BBB', 0, 0) 
Insert into tb1 values (2, 'CCC', 0, 0) 
The create table tb2 (id int, xm_dm varchar (10), je int) 
Insert into tb2 values (1, '01, 100) 
Insert into tb2 values (1, '01, 110) 
Insert into tb2 values (1, '02, 50) 
Insert into tb2 values (1, '3', 200) 
Insert into tb2 values (2, '02, 330) 
Insert into tb2 values (2, '02, 20) 
Insert into tb2 values (2, '3', 80) 
Go 

The update tb1 
The set je1=isnull ((select sum (je) from tb2 where id=tb1. The id and tb2. Xm_dm='01'), 0), 
Je2=isnull ((select sum (je) from tb2 where id=tb1. The id and tb2. Xm_dm='02'), 0), 
Je3=isnull ((select sum (je) from tb2 where id=tb1. The id and tb2. Xm_dm='03'), 0) 

Select * from tb1 

Drop table tb1, tb2 

/* 
Id name je1 je2 je3 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
1 aaa 210 50 200 
2 BBB 0, 350, 80 
2 CCC 0 350 80 

(influenced by the number of rows for 3 lines) 
*/

Don't know you
2 CCC
Why the record je1 je2, je3 no value?

CodePudding user response:

unreasonable

CodePudding user response:

Thank you expert answers,
Correct the third row ids in the table1 should be 3.
Table1
Id name je1 je2 je3
1 aaa
2 BBB
3 CCC

CodePudding user response:

Thanks for experts to solve and correct the third row ids in the table1 should be 3.
Table1
Id name je1 je2 je3
1 aaa
2 BBB
3 CCC

CodePudding user response:

Thanks for experts to solve and correct the third row ids in the table1 should be 3.
Table1
Id name je1 je2 je3
1 aaa
2 BBB
3 CCC

CodePudding user response:

Not a statement, the UPDATE does not support