2个表，想达到图片中蓝色表格的统计效果，请问 SQL该怎么写？
表设计是不是有问题，有问题的话可以改。多谢
附2个表创建的SQL
/************************收入表
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Income](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DepartmentID] [int] NULL,
[IMoney] [decimal](10, 2) NULL,
[IResource] [nvarchar](200) NULL,
[IRemark] [nvarchar](200) NULL,
[ITime] [datetime] NULL,
CONSTRAINT [PK_Income] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
/************************支出表
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Pay](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DepartmentID] [int] NULL,
[PMoney] [decimal](10, 2) NULL,
[PResource] [nvarchar](200) NULL,
[PRemark] [nvarchar](200) NULL,
[PTime] [datetime] NULL,
CONSTRAINT [PK_Pay] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
------解决思路----------------------
SELECT
ISNULL(A.[DepartmentID],B.[DepartmentID])[DepartmentID]
,A.[ITime]'进账日期'
,A.[IMoney]'进账金额'
,A.[IResource]'进账来源'
,B.[PTime]'出账日期'
,B.[PMoney]'出账金额'
,B.[PResource]'出账原因'
FROM
[Income] A
FULL JOIN [Pay] B ON A.[DepartmentID]=B.[DepartmentID]AND A.[ITime]=B.[PTime]
ORDER BY
A.[ITime]
语句类似这样，而合并表格，你需要在显示上做处理，有什么问题贴上来吧~~
------解决思路----------------------
with income as
(select 1 as id,27 as departmentid,100.00 as imoeny,'一部进账' as iresource,
null as iremark, '2014-10-11 00:00:00.000' as itime union all
select 2 as id,28 as departmentid,200.00 as imoeny,'二部进账' as iresource,
null as iremark, '2014-10-11 00:00:00.000' as itime),
pay as
(select 1 as id, 27 as departmentid,50.00 as pmoney,'一部支出' as presource,
null as premark ,'2014-10-11 00:00:00.000' as ptime union all
select 2 as id, 28 as departmentid,90.00 as pmoney,'二部支出' as presource,
null as premark ,'2014-10-11 00:00:00.000' as ptime )
select left(iresource,2)as 部门,
itime ,imoeny ,iremark ,
ptime,pmoney,premark,imoeny-pmoney as 结余
from income as a join
pay as b on a.departmentid=b.departmentid
--结果
部门 itime imoeny iremark ptime pmoney premark 结余
---- ----------------------- --------------------------------------- ----------- ----------------------- --------------------------------------- ----------- ---------------------------------------
一部 2014-10-11 00:00:00.000 100.00 NULL 2014-10-11 00:00:00.000 50.00 NULL 50.00
二部 2014-10-11 00:00:00.000 200.00 NULL 2014-10-11 00:00:00.000 90.00 NULL 110.00