Two table SQL query computation to generate another table

CodePudding user response:

How do you write the code?

CodePudding user response:

Select out the column into the generated from table 1 inner join table 2 on the where condition

CodePudding user response:

The test data

 -Select * # into a table from (select 1 as device number, amount, 20 'cash top-up' settlement way union all 
Select 1 as device number, 30 money, 'open card deposit payment union all 
Select 1 as the device number, amount, 40 'cash settlement way union all 
Select 2 as device number, amount, 50 'timing and settlement and settlement way union all 
Select 2 as device number, amount, 60 a 'cash top-up' settlement way union all 
Select 3 as device number, 70 money, a 'cash top-up' settlement way union all 
Select 3 as device number, 80 money, the method of payment) as a 'cash top-up' 

Select * # into table 2 the from (select 1 as device number, balance of 20, 20 deposit union all 
Select 2 as device number, balance of 20, 20 deposit union all 
Select 3 as device number, balance of 20, 20) the deposit as a 
- 
Device number, select a. a. cash top-up, a. open card deposit, isnull (a. timing settlement, 0) + isnull (a. cash consumption, 0) as' cash consumption + settlement time, deposit, balance of b. b. the from 
# (select * from table 1 as a pivot (sum (money) for a. the method of payment in (cash top-up, open card deposit, cash, timing and settlement)) as b) on a table join # 2 b a. device number=b. device number 

/* the results 
1 20 20 20 30 to 40 
2 60 NULL 50 20 20 
3 150 NULL 0 20 20 
*/