CREATE TABLE 收入表(TransNo VARCHAR(10),    Amount INT ,    CreateDate DATETIME)
 INSERT INTO 收入表
 SELECT '1',    100    ,'2012-10-02 00:00:00.000'
 UNION ALL 
 SELECT '1',    100    ,'2012-10-02 17:41:16.723'
 UNION ALL 
 SELECT '03',    12312    ,'2012-10-02 17:43:36.483'
 UNION ALL 
 SELECT '1231',    12312,    '2012-10-02 17:44:58.303'
 UNION ALL 
 SELECT '12312',    13231,    '2012-10-02 17:49:16.250'
 
 CREATE TABLE 支出表(TransNo VARCHAR(10),    Amount INT ,    CreateDate DATETIME)
 INSERT INTO 支出表
 SELECT '1',    100,'2012-10-03 00:00:00.000'
 UNION ALL 
 SELECT '01',    122,    '2012-10-03 09:36:54.103'
 UNION ALL 
 SELECT '03',    -200,    '2012-10-03 09:40:01.557'
 
 SELECT CONVERT(DATE,createdate) createdate,ISNULL(CASE WHEN [STATUS]='in' THEN amount END,0) [收入],ISNULL(CASE WHEN [STATUS]='out' THEN amount END,0) [支出] 
 FROM (
 SELECT TransNo,    Amount,    CreateDate,'in' [STATUS]
 FROM 收入表 
 UNION ALL 
 SELECT TransNo,    Amount,    CreateDate,'out' [STATUS]
 FROM  支出表 )a
 
 /*
 createdate 收入          支出
 ---------- ----------- -----------
 2012-10-02 100         0
 2012-10-02 100         0
 2012-10-02 12312       0
 2012-10-02 12312       0
 2012-10-02 13231       0
 2012-10-03 0           100
 2012-10-03 0           122
 2012-10-03 0           -200
 
 (8 行受影响)
 
 
 
 */

------解决方案--------------------

SQL code



DECLARE @income TABLE
(
    TransNo varchar(10),
    Amount decimal,
    CreateDate datetime
);
DECLARE @pay table
(
    TransNo varchar(10),
    Amount decimal,
    CreateDate datetime
);

INSERT INTO @income
    SELECT '1',100, '2012-10-02 00:00:00'
    UNION ALL
    SELECT '1',100, '2012-10-02 17:41:36'
    UNION ALL
    SELECT '03',12312, '2012-10-02 17:43:36'
    UNION ALL 
    SELECT '1231',12312, '2012-10-02 17:44:58'
    UNION ALL 
    SELECT '12312',13231, '2012-10-02 17:49:16'

INSERT INTO @pay
    SELECT '1',100, '2012-10-02 00:00:00'
    UNION ALL
    SELECT '01',12312, '2012-10-02 17:43:36'
    UNION ALL 
    SELECT '03', -200, '2012-10-03 17:44:58'


;WITH c1 AS
(
select SUM(Amount) as cost, convert(varchar(10), CreateDate,20) as 'CreateDate', '收入' AS [State]
from @income group by convert(varchar(10), CreateDate,20)
UNION ALL
select SUM(Amount) as cost, convert(varchar(10), CreateDate,20) as 'CreateDate', '支出'
from @pay group by convert(varchar(10), CreateDate,20)
)
SELECT 
    c1.CreateDate,
    SUM(CASE WHEN c1.[state] = '收入' THEN c1.cost ELSE NULL END) '收入',
    SUM(CASE WHEN c1.[state] = '支出' THEN c1.cost ELSE NULL END) '支出'
FROM c1
GROUP BY c1.CreateDate