select a.date,b.收入金额,a.支出金额,b.收入金额-a.支出金额 from ( select a.date, sum(支出金额) as 支出金额 from tb as a group by convert(nvarchar(7),date,120) )a inner join ( select a.date, sum(收入金额 ) as 收入金额 from tb as a group by convert(nvarchar(7),date,120) ) b on a.date= b.date
楼主给出的数据有点点问题--> 测试数据:[收入表A] if object_id('[收入表A]') is not null drop table [收入表A] create table [收入表A]([收入日期] datetime,[收入金额] int) insert [收入表A] select '2010-03-10 12:44:37',100 union all select '2010-03-11 12:04:30',80 union all select '2010-04-01 10:12:33',50 --> 测试数据:[支出表B] if object_id('[支出表B]') is not null drop table [支出表B] create table [支出表B]([支出日期] datetime,[支出金额] int) insert [支出表B] select '2010-04-01 12:44:37',100 union all select '2010-04-11 11:34:10',180 union all select '2010-06-11 08:12:33',500select 收支日期=日期, 收入金额=sum(case when 金额>0 then 金额 else 0 end), 支出金额=sum(case when 金额<0 then 金额 else 0 end), 收支差额=sum(金额) from ( select 日期=convert(varchar(7),[收入日期],120),金额=[收入金额] from [收入表A] union all select convert(varchar(7),[支出日期],120),-[支出金额] from [支出表B])g group by 日期 order by 日期/* 收支日期 收入金额 支出金额 收支差额 ------- ----------- ----------- ----------- 2010-03 180 0 180 2010-04 50 -280 -230 2010-06 0 -500 -500(3 行受影响)*/drop table [收入表A] drop table [支出表B]
这帮SQL大人牛逼啊。。写的很详细、、我就不说了。。
我感觉SQL版里面的那些牛人写SQL语句比我算加法还快 不能不服
回头看一下, 好像自己写错了.. 改了改~ declare @a TABLE ([date] datetime,[收入金额] int) insert @a select '2010-03-10 12:44:37',100 union all select '2010-03-11 12:04:30',80 union all select '2010-04-01 10:12:33',50 declare @b TABLE (date datetime,[支出金额] int) insert @b select '2010-04-01 12:44:37',100 union all select '2010-04-11 11:34:10',180 union all select '2010-06-11 08:12:33',500 select ISNULL (a.date,b.date) AS date, ISNULL(b.收入金额,0) as 收入金额, isnull(a.支出金额,0) as 支出金额, ISNULL(b.收入金额,0)-isnull(a.支出金额,0) from ( select convert(nvarchar(7),date,120) AS [date], sum(支出金额) as 支出金额 from @b as a group by convert(nvarchar(7),date,120) )a FULL join ( select convert(nvarchar(7),date,120) AS [date], sum(收入金额 ) as 收入金额 from @a as a group by convert(nvarchar(7),date,120) ) b on a.date= b.date
select a.date,b.收入金额,a.支出金额,b.收入金额-a.支出金额
from
(
select a.date, sum(支出金额) as 支出金额
from tb as a
group by convert(nvarchar(7),date,120)
)a inner join
(
select a.date, sum(收入金额 ) as 收入金额
from tb as a
group by convert(nvarchar(7),date,120)
) b on a.date= b.date
if object_id('[收入表A]') is not null drop table [收入表A]
create table [收入表A]([收入日期] datetime,[收入金额] int)
insert [收入表A]
select '2010-03-10 12:44:37',100 union all
select '2010-03-11 12:04:30',80 union all
select '2010-04-01 10:12:33',50
--> 测试数据:[支出表B]
if object_id('[支出表B]') is not null drop table [支出表B]
create table [支出表B]([支出日期] datetime,[支出金额] int)
insert [支出表B]
select '2010-04-01 12:44:37',100 union all
select '2010-04-11 11:34:10',180 union all
select '2010-06-11 08:12:33',500select 收支日期=日期,
收入金额=sum(case when 金额>0 then 金额 else 0 end),
支出金额=sum(case when 金额<0 then 金额 else 0 end),
收支差额=sum(金额)
from (
select 日期=convert(varchar(7),[收入日期],120),金额=[收入金额] from [收入表A]
union all
select convert(varchar(7),[支出日期],120),-[支出金额] from [支出表B])g
group by 日期 order by 日期/*
收支日期 收入金额 支出金额 收支差额
------- ----------- ----------- -----------
2010-03 180 0 180
2010-04 50 -280 -230
2010-06 0 -500 -500(3 行受影响)*/drop table [收入表A]
drop table [支出表B]
不能不服
declare @a TABLE ([date] datetime,[收入金额] int)
insert @a
select '2010-03-10 12:44:37',100 union all
select '2010-03-11 12:04:30',80 union all
select '2010-04-01 10:12:33',50
declare @b TABLE (date datetime,[支出金额] int)
insert @b
select '2010-04-01 12:44:37',100 union all
select '2010-04-11 11:34:10',180 union all
select '2010-06-11 08:12:33',500
select ISNULL (a.date,b.date) AS date,
ISNULL(b.收入金额,0) as 收入金额,
isnull(a.支出金额,0) as 支出金额,
ISNULL(b.收入金额,0)-isnull(a.支出金额,0)
from
(
select convert(nvarchar(7),date,120) AS [date], sum(支出金额) as 支出金额
from @b as a
group by convert(nvarchar(7),date,120)
)a FULL join
(
select convert(nvarchar(7),date,120) AS [date], sum(收入金额 ) as 收入金额
from @a as a
group by convert(nvarchar(7),date,120)
) b on a.date= b.date