销售表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就是同时得到两个表的数据,并且带子查询的。
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就是同时得到两个表的数据,并且带子查询的。
select
addtime = coalesce(A.sale_addtime, B.saleout_addtime),
sale_money = isnull(A.sale_money, 0),
saleout_money = isnull(B.saleout_money, 0),
[money] = (isnull(A.sale_money, 0) - isnull(B.saleout_money, 0))
from
(
select
sale_addtime,
sale_money =sum(sale_money)
from x_sale
group by sale_addtime
) A
full join
(
select
saleout_addtime,
saleout_money =sum(saleout_money)
from x_saleout
group by saleout_addtime
) B
on A.sale_addtime = B.saleout_addtime
--> 测试数据:[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 all
select 2,'2012-01-01',500 union all
select 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 all
select 1,'2012-01-01',100 union all
select 2,'2012-01-03',200
with t
as(
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 800
2012-01-02 00:00:00.000 300 0 300
2012-01-03 00:00:00.000 0 200 -200
*/