select a.prd,a.dept,a.date,isnull(a.money,0)+ as 发货金额,isnull(b.money) as 退货金额,(isnull(a.money,0)-isnull(b.money,0)) as 净额
from [发货数据] a inner join [退货数据] b
on a.prd = b.prd and a.date = b.date and a.dept = b.dept
from [发货数据] a inner join [退货数据] b
on a.prd = b.prd and a.date = b.date and a.dept = b.dept
from
(
select prd,dept,[date],[money]
from 发货数据
union all
select prd,dept,[date],-[money]
from 退货数据
)
group by prd,dept,convert(char(7),[date],120)
FROM
(SELECT prd, SUM(money) AS fahuo
FROM 发货数据
WHERE dept='销售一部'
AND date='2004-02-11'
GROUP BY prd) f LEFT OUTER JOIN
(SELECT prd, SUM(money) AS tuihuo
FROM 退货数据
WHERE dept='销售一部'
AND date='2004-02-11'
GROUP BY prd) t
ON f.prd=t.prd
from
(
select dept , left([date] , 7) as [date] , sum(money) as mny
from 发货表
group by dept , left([date] , 7)
) a
full outer join
(
select dept , left([date] , 7) as [date] , sum(money) as mny
from 退货表
group by dept , left([date] , 7)
) b on a.[date] = b.[date] and a.dept = b.dept
from
(
select dept , left([date] , 7) as [date] , sum(money) as mny
from 发货表
group by dept , left([date] , 7)
) a
full outer join
(
select dept , left([date] , 7) as [date] , sum(money) as mny
from 退货表
group by dept , left([date] , 7)
) b on a.[date] = b.[date] and a.dept = b.dept
(prd varchar(20),dept varchar(30),date datetime,money decimal(10,2))insert @t1
select '美标','销售一部','2004-02-12',1532.00 union all
select '美标','销售二部','2004-04-24',5030.00 union all
select '东鹏','销售一部','2004-03-23',15660.00 union all
select '美标','销售二部','2005-08-06',350.00
declare @t2 table
(prd varchar(20),date datetime,dept varchar(30),money decimal(10,2))insert @t2
select '美标','2004-02-11','销售一部',440.00 union all
select '美标','2004-05-14','销售二部',5729.00 union all
select '东鹏','2004-11-21','销售一部',1343.04 union all
select '得而达','2005-08-28','销售二部',1000.00select isnull(a.dept,b.dept) as dept,
isnull(a.date,b.date) as date,isnull(a.money,0)-isnull(b.money,0) as expt from
(
select dept,convert(char(7),date,120) as date,sum(money) as money from @t1 group by
dept,convert(char(7),date,120)
) a full join
(
select dept,convert(char(7),date,120) as date,sum(money) as money from @t2 group by
dept,convert(char(7),date,120)
) b on a.dept=b.dept and a.date=b.datedept date expt
------------------------------ ------- ----------------------------------------
销售二部 2004-04 5030.00
销售二部 2004-05 -5729.00
销售二部 2005-08 -650.00
销售一部 2004-02 1092.00
销售一部 2004-03 15660.00
销售一部 2004-11 -1343.04(所影响的行数为 6 行)