销售表x_sale
sale_id sale_addtime sale_money
1 2012-01-01 500
2 2012-01-01 500
3 2012-01-02 300
销售退货表x_saleout
saleout_id saleout_addtime saleout_money
1 2012-01-01 100
1 2012-01-01 100
2 2012-01-03 200
如何才能得到
addtime sale_money saleout_money money
2012-01-01 1000 200 800
2012-01-02 300 0 300
2012-01-03 0 200 -200
就是同时得到两个表的数据，并且带子查询的。
------解决方案--------------------
- SQL code
--> 测试数据：[x_sale]if object_id('[x_sale]') is not null drop table [x_sale]create table [x_sale]([sale_id] int,[sale_addtime] datetime,[sale_money] int)insert [x_sale]select 1,'2012-01-01',500 union allselect 2,'2012-01-01',500 union allselect 3,'2012-01-02',300--> 测试数据：[x_saleout]if object_id('[x_saleout]') is not null drop table [x_saleout]create table [x_saleout]([saleout_id] int,[saleout_addtime] datetime,[saleout_money] int)insert [x_saleout]select 1,'2012-01-01',100 union allselect 1,'2012-01-01',100 union allselect 2,'2012-01-03',200with tas(select [sale_addtime], SUM([sale_money]) [sale_money]from [x_sale]group by [sale_addtime]),m as(select [saleout_addtime], SUM([saleout_money]) as [saleout_money]from [x_saleout]group by [saleout_addtime])select isnull(t.sale_addtime,m.saleout_addtime) as addtime, isnull(t.sale_money,0) as sale_money, isnull(m.saleout_money,0) as saleout_money, isnull(t.sale_money,0)-isnull(m.saleout_money,0) as [money]from t full join m on t.sale_addtime=m.saleout_addtime/*addtime sale_money saleout_money money---------------------------------------------------2012-01-01 00:00:00.000 1000 200 8002012-01-02 00:00:00.000 300 0 3002012-01-03 00:00:00.000 0 200 -200*/