收入表:
TransNo Amount CreateDate
1 100 2012-10-02 00:00:00.000
1 100 2012-10-02 17:41:16.723
03 12312 2012-10-02 17:43:36.483
1231 12312 2012-10-02 17:44:58.303
12312 13231 2012-10-02 17:49:16.250支出表
TransNo Amount CreateDate
1 100 2012-10-03 00:00:00.000
01 122 2012-10-03 09:36:54.103
03 -200 2012-10-03 09:40:01.557我希望根据收入和支出表显示出:
日期 收入 支出
20120901 1000 200
20120902 1000 200按天分别计算收入和支出总和,请问各位大拿,sql语句咋写呢?
TransNo Amount CreateDate
1 100 2012-10-02 00:00:00.000
1 100 2012-10-02 17:41:16.723
03 12312 2012-10-02 17:43:36.483
1231 12312 2012-10-02 17:44:58.303
12312 13231 2012-10-02 17:49:16.250支出表
TransNo Amount CreateDate
1 100 2012-10-03 00:00:00.000
01 122 2012-10-03 09:36:54.103
03 -200 2012-10-03 09:40:01.557我希望根据收入和支出表显示出:
日期 收入 支出
20120901 1000 200
20120902 1000 200按天分别计算收入和支出总和,请问各位大拿,sql语句咋写呢?
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 行受影响)
*/
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,SUM(ISNULL(CASE WHEN [STATUS]='in' THEN amount END,0)) [收入],SUM(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
GROUP BY CONVERT(DATE,createdate)
/*
createdate 收入 支出
---------- ----------- -----------
2012-10-02 38055 0
2012-10-03 0 22
(2 行受影响)
*/
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试试看可不可以……