多表联合查询的问题《多表联合查询的问题？

[goodluck2017]

select a.corp_id,a.corp_name,(select sum(b.sale_money) from x_sale sale_corp=a.corp_id)as salemoney,(select sum(b.saleout_money) from x_saleout saleout_corp=a.corp_id)as saleoutmoney,这里怎么写才可以salemoney和saleoutmoney 相减 from x_kcorp a

[百年树人]

select 
  a.corp_id,
  a.corp_name,
  b.salemoney,
  c.saleoutmoney,
  b.salemoney-c.saleoutmoney
from x_kcorp a
join (select sale_corp,sum(sale_money) as salemoney from x_sale group by sale_corp) b
  on a.corp_id=b.sale_corp
join (select saleout_corp,sum(saleout_money) as saleoutmoney from x_saleout group by saleout_corp) c
  on a.a.corp_id=c.saleout_corp

[Shawn]

use CSDN
go

--#1.直接减就可以
select 
	a.corp_id,
	a.corp_name,
	(select sum(b.sale_money) from x_sale where sale_corp=a.corp_id) as salemoney,
	(select sum(b.saleout_money) from x_saleout where saleout_corp=a.corp_id) as saleoutmoney,
	diff = (select sum(b.sale_money) from x_sale where sale_corp=a.corp_id) - (select sum(b.saleout_money) from x_saleout where saleout_corp=a.corp_id)
from x_kcorp a

--#2.或者用子查询
select 
	*,
	diff = salemoney - saleoutmoney
from
(
	select 
		a.corp_id,
		a.corp_name,
		(select sum(b.sale_money) from x_sale where sale_corp=a.corp_id) as salemoney,
		(select sum(b.saleout_money) from x_saleout where saleout_corp=a.corp_id) as saleoutmoney
	from x_kcorp a
) T

[goodluck2017]

谢谢大家，如果还要用diff = salemoney - saleoutmoney的值当子查询，如
gmoney=diff*100,应该如何写

[百年树人]

[Quote=引用 3 楼 的回复:]
谢谢大家，如果还要用diff = salemoney - saleoutmoney的值当子查询，如
gmoney=diff*100,应该如何写
[/Quote]
参考1楼，直接(b.salemoney-c.saleoutmoney)*100

[goodluck2017]

如果去掉diff = salemoney - saleoutmoney 为0的记录

[百年树人]

[Quote=引用 5 楼 的回复:]
如果去掉diff = salemoney - saleoutmoney 为0的记录
[/Quote]

select 
  a.corp_id,
  a.corp_name,
  b.salemoney,
  c.saleoutmoney,
  b.salemoney-c.saleoutmoney as diff
from x_kcorp a
join (select sale_corp,sum(sale_money) as salemoney from x_sale group by sale_corp) b
  on a.corp_id=b.sale_corp
join (select saleout_corp,sum(saleout_money) as saleoutmoney from x_saleout group by saleout_corp) c
  on a.a.corp_id=c.saleout_corp
where b.salemoney-c.saleoutmoney!=0

[goodluck2017]

select
*,
diff = salemoney - saleoutmoney
from
(
select
a.corp_id,
a.corp_name,
(select sum(b.sale_money) from x_sale where sale_corp=a.corp_id) as salemoney,
(select sum(b.saleout_money) from x_saleout where saleout_corp=a.corp_id) as saleoutmoney
from x_kcorp a
) T
如何去掉realmoney为零的记录

[百年树人]

select  
  *,
  diff = salemoney - saleoutmoney
from
(
  select  
  a.corp_id,
  a.corp_name,
  (select sum(b.sale_money) from x_sale where sale_corp=a.corp_id) as salemoney,
  (select sum(b.saleout_money) from x_saleout where saleout_corp=a.corp_id) as saleoutmoney
  from x_kcorp a
) T
where salemoney - saleoutmoney!=0