SQL两个表查询计算生成另一个表

[Mqs15802245582]

SQL两个表查询计算生成另一个表

[q159846]

select 查出的列名 into 生成的表 from 表1 inner join 表2 on 关联条件 where 条件

[Mqs15802245582]

代码怎么写？

[XXXXn帅气]

--测试数据
select* into #表一 from (select 1 as 设备号,20 钱数,'现金充值' 结算方式 union all
						select 1 as 设备号,30 钱数,'开卡押金' 结算方式 union all  
						select 1 as 设备号,40 钱数,'现金消费' 结算方式 union all 
						select 2 as 设备号,50 钱数,'计时结算' 结算方式 union all 
						select 2 as 设备号,60 钱数,'现金充值' 结算方式 union all 
						select 3 as 设备号,70 钱数,'现金充值' 结算方式 union all 
						select 3 as 设备号,80 钱数,'现金充值' 结算方式)as a

select * into #表二 from (select 1 as 设备号,20 余额,20 押金 union all
						select 2 as 设备号,20 余额,20 押金 union all
						select 3 as 设备号,20 余额,20 押金) as a
--
select a.设备号,a.现金充值,a.开卡押金,isnull(a.计时结算,0)+isnull(a.现金消费,0)as '现金消费+计时结算',b.押金,b.余额 from 
( select * from #表一 as a pivot(sum(钱数) for a.结算方式 in(现金充值,开卡押金,现金消费,计时结算))as b) a join #表二 b on a.设备号=b.设备号

/*结果
1	20	30	40	20	20
2	60	NULL	50	20	20
3	150	NULL	0	20	20
*/