请问一条复杂的sql语句 ，在线等答案！
收入表：
TransNoAmountCreateDate
11002012-10-02 00:00:00.000
11002012-10-02 17:41:16.723
03123122012-10-02 17:43:36.483
1231123122012-10-02 17:44:58.303
12312132312012-10-02 17:49:16.250

支出表
TransNoAmountCreateDate
11002012-10-03 00:00:00.000
011222012-10-03 09:36:54.103
03-2002012-10-03 09:40:01.557

我希望根据收入和支出表显示出：
日期 收入 支出
20120901 1000 200
20120902 1000 200

按天分别计算收入和支出总和，请问各位大拿，sql语句咋写呢？

[解决办法]

SQL code

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 ALLselect 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 c1GROUP BY c1.CreateDate