表属性:
Column name Date type
id int
money money
date datetime
re ntext
addTF bit
供参考数据如下:
id money date re addTF
-- ---------- ---------- ------ -----
1 2.0000 2010-3-12 山东省 1
4 34.0000 2010-8-19 说到底 1
22 23.0000 2010-8-19 法萨芬 1
24 23.0000 2010-8-21 法萨芬 0
10 77.0000 2010-8-19 啊 1
11 7788.0000 2010-10-23 爱的 0
12 123.0000 2010-8-19 gg各分管 1
13 321.0000 2010-8-19 和规范 1
14 23.0000 2010-8-19 123 1
15 23432.0000 2010-8-19 撒旦 1
16 454.0000 2010-8-19 各分管 1
17 454.0000 2010-8-19 各分管 1
18 77.0000 2010-8-19 啊 1
19 231.0000 2010-8-21 adssd 0
20 231.0000 2010-8-21 adssd 0
21 676.0000 2010-8-20 的说法 1
25 676.0000 2010-8-20 的说法 1
26 454.0000 2010-8-19 的说法 1
27 12.0000 1999-12-1 a撒旦 1
28 231.0000 2010-7-19 倒萨 1(20 row(s) selected in time: 00:00).
PS:addTF的true(显示为1)表示收入,falsh(显示为0)表示支出目标:求出每个月的结余金额。
Column name Date type
id int
money money
date datetime
re ntext
addTF bit
供参考数据如下:
id money date re addTF
-- ---------- ---------- ------ -----
1 2.0000 2010-3-12 山东省 1
4 34.0000 2010-8-19 说到底 1
22 23.0000 2010-8-19 法萨芬 1
24 23.0000 2010-8-21 法萨芬 0
10 77.0000 2010-8-19 啊 1
11 7788.0000 2010-10-23 爱的 0
12 123.0000 2010-8-19 gg各分管 1
13 321.0000 2010-8-19 和规范 1
14 23.0000 2010-8-19 123 1
15 23432.0000 2010-8-19 撒旦 1
16 454.0000 2010-8-19 各分管 1
17 454.0000 2010-8-19 各分管 1
18 77.0000 2010-8-19 啊 1
19 231.0000 2010-8-21 adssd 0
20 231.0000 2010-8-21 adssd 0
21 676.0000 2010-8-20 的说法 1
25 676.0000 2010-8-20 的说法 1
26 454.0000 2010-8-19 的说法 1
27 12.0000 1999-12-1 a撒旦 1
28 231.0000 2010-7-19 倒萨 1(20 row(s) selected in time: 00:00).
PS:addTF的true(显示为1)表示收入,falsh(显示为0)表示支出目标:求出每个月的结余金额。
id int,
money money,
date datetime,
re NTEXT,
addTF bit)
insert into #T SELECT 1, 2.0000,'2010-3-12','山东省', 1 UNION ALL
SELECT 4 ,34.0000,'2010-8-19','说到底', 1 UNION ALL
SELECT 22, 23.0000,'2010-8-19','法萨芬', 1 UNION ALL
SELECT 24, 23.0000,'2010-8-21','法萨芬', 0 UNION ALL
SELECT 10, 77.0000,'2010-8-19','啊', 1 UNION ALL
SELECT 11, 7788.0000,'2010-10-23','爱的', 0 UNION ALL
SELECT 12, 123.0000 ,'2010-8-19','gg各分管', 1 UNION ALL
SELECT 13 ,321.0000,'2010-8-19','和规范', 1 UNION ALL
SELECT 14, 23.0000 ,'2010-8-19','123', 1 UNION ALL
SELECT 15, 23432.0000,'2010-8-19','撒旦', 1 UNION ALL
SELECT 16, 454.0000 ,'2010-8-19','各分管', 1 UNION ALL
SELECT 17, 454.0000 ,'2010-8-19','各分管', 1 UNION ALL
SELECT 18 ,77.0000 ,'2010-8-19','啊', 1 UNION ALL
SELECT 19, 231.0000,'2010-8-21','adssd', 0 UNION ALL
SELECT 20, 231.0000,'2010-8-21','adssd', 0 UNION ALL
SELECT 21, 676.0000,'2010-8-20','的说法', 1 UNION ALL
SELECT 25, 676.0000,'2010-8-20','的说法', 1 UNION ALL
SELECT 26, 454.0000,'2010-8-19','的说法', 1 UNION ALL
SELECT 27, 12.0000,'1999-12-1','a撒旦',1 UNION ALL
SELECT 28, 231.0000,'2010-7-19','倒萨', 1
SELECT convert(varchar(6),date,112) '月度',sum(money*case when addTF=0 then -1 else 1 end) '金额'
FROM #T group by convert(varchar(6),date,112) 月度 金额
------ ---------------------
199912 12.00
201003 2.00
201007 231.00
201008 26339.00
201010 -7788.00(5 行受影响)
id int,
money money,
date datetime,
re NTEXT,
addTF bit)
insert into #T SELECT 1, 2.0000,'2010-3-12','山东省', 1 UNION ALL
SELECT 4 ,34.0000,'2010-8-19','说到底', 1 UNION ALL
SELECT 22, 23.0000,'2010-8-19','法萨芬', 1 UNION ALL
SELECT 24, 23.0000,'2010-8-21','法萨芬', 0 UNION ALL
SELECT 10, 77.0000,'2010-8-19','啊', 1 UNION ALL
SELECT 11, 7788.0000,'2010-10-23','爱的', 0 UNION ALL
SELECT 12, 123.0000 ,'2010-8-19','gg各分管', 1 UNION ALL
SELECT 13 ,321.0000,'2010-8-19','和规范', 1 UNION ALL
SELECT 14, 23.0000 ,'2010-8-19','123', 1 UNION ALL
SELECT 15, 23432.0000,'2010-8-19','撒旦', 1 UNION ALL
SELECT 16, 454.0000 ,'2010-8-19','各分管', 1 UNION ALL
SELECT 17, 454.0000 ,'2010-8-19','各分管', 1 UNION ALL
SELECT 18 ,77.0000 ,'2010-8-19','啊', 1 UNION ALL
SELECT 19, 231.0000,'2010-8-21','adssd', 0 UNION ALL
SELECT 20, 231.0000,'2010-8-21','adssd', 0 UNION ALL
SELECT 21, 676.0000,'2010-8-20','的说法', 1 UNION ALL
SELECT 25, 676.0000,'2010-8-20','的说法', 1 UNION ALL
SELECT 26, 454.0000,'2010-8-19','的说法', 1 UNION ALL
SELECT 27, 12.0000,'1999-12-1','a撒旦',1 UNION ALL
SELECT 28, 231.0000,'2010-7-19','倒萨', 1
SELECT convert(varchar(6),date,112) '月度',sum(money*case when addTF=0 then -1 else 1 end) '金额'
FROM #T group by convert(varchar(6),date,112) 月度 金额
------ ---------------------
199912 12.00
201003 2.00
201007 231.00
201008 26339.00
201010 -7788.00(5 行受影响)
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int,[money] numeric(9,4),date datetime,re varchar(8),addTF int)
insert into #tb
select 1,2.0000,'2010-3-12','山东省',1 union all
select 4,34.0000,'2010-8-19','说到底',1 union all
select 22,23.0000,'2010-8-19','法萨芬',1 union all
select 24,23.0000,'2010-8-21','法萨芬',0 union all
select 10,77.0000,'2010-8-19','啊',1 union all
select 11,7788.0000,'2010-10-23','爱的',0 union all
select 12,123.0000,'2010-8-19','gg各分管',1 union all
select 13,321.0000,'2010-8-19','和规范',1 union all
select 14,23.0000,'2010-8-19','123',1 union all
select 15,23432.0000,'2010-8-19','撒旦',1 union all
select 16,454.0000,'2010-8-19','各分管',1 union all
select 17,454.0000,'2010-8-19','各分管',1 union all
select 18,77.0000,'2010-8-19','啊',1 union all
select 19,231.0000,'2010-8-21','adssd',0 union all
select 20,231.0000,'2010-8-21','adssd',0 union all
select 21,676.0000,'2010-8-20','的说法',1 union all
select 25,676.0000,'2010-8-20','的说法',1 union all
select 26,454.0000,'2010-8-19','的说法',1 union all
select 27,12.0000,'1999-12-1','a撒旦',1 union all
select 28,231.0000,'2010-7-19','倒萨',1select year(date) as y,month(date) as m,
je=sum(case when addTF=1 then [money] else -[money] end )
from #tb
group by year(date) ,month(date)
y m je
----------- ----------- ---------------------------------------
2010 3 2.0000
2010 7 231.0000
2010 8 26339.0000
2010 10 -7788.0000
1999 12 12.0000(5 行受影响)
select convert(varchar(7),date, 120) mouth, money*(case when addTF=0 then -1 else addTF end) from 表
group by convert(varchar(7),date, 120)
group by convert(varchar(7),date, 120)
楼上所有方法都试过了,还不行。其中5楼的方法提示错误是:
ERROR (Row 0);
Microsoft SQL Server Compact OLE DB Provider:
"分析查询时出错。(Token line number: 2, Token line offset: 3, Token in error: "=" )"
SELECT convert(nvarchar(6),date,120) as 月度,sum(money*case when addTF=0 then -1 else 1 end) as 金额
FROM financing group by convert(nvarchar(6),date,120)