SELECT Kind,
MAX(CASE [Month] WHEN 1 THEN Num ELSE 0 END) AS 一月,
MAX(CASE [Month] WHEN 2 THEN Num ELSE 0 END) AS 二月,
MAX(CASE [Month] WHEN 3 THEN Num ELSE 0 END) AS 三月,
MAX(CASE [Month] WHEN 4 THEN Num ELSE 0 END) AS 四月,
MAX(CASE [Month] WHEN 5 THEN Num ELSE 0 END) AS 五月,
MAX(CASE [Month] WHEN 6 THEN Num ELSE 0 END) AS 六月,
MAX(CASE [Month] WHEN 7 THEN Num ELSE 0 END) AS 七月,
MAX(CASE [Month] WHEN 8 THEN Num ELSE 0 END) AS 八月,
MAX(CASE [Month] WHEN 9 THEN Num ELSE 0 END) AS 九月,
MAX(CASE [Month] WHEN 10 THEN Num ELSE 0 END) AS 十月,
MAX(CASE [Month] WHEN 11 THEN Num ELSE 0 END) AS 十一月,
MAX(CASE [Month] WHEN 12 THEN Num ELSE 0 END) AS 十二月
FROM (
SELECT SUM(CAST(e.Money AS Float)) AS Num, FK.Kind, MONTH(e.Date) AS Month
FROM dbo.Expense AS e LEFT OUTER JOIN
dbo.BKind AS k ON e.KindID = k.ID LEFT OUTER JOIN
dbo.BKind AS FK ON k.FID = FK.ID
WHERE (YEAR(e.Date) =@Year) AND (FK.ID =0)
GROUP BY FK.Kind, MONTH(e.Date)
) AS s
group by Kind
END这个存储过程统计不到差旅费,我要的是一个总的合计,横排12个月12个合计的。数据表 Kind表1 差旅费 0
2 餐费 0
3 办公费 0
4 通讯费 0
5 其他 0
6 火车票 1
7 汽车票 1
8 出租车票 1
9 飞机票 1
10 住宿费 0
0 合计 2
表Month
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12报销表 Expense
93 GC0001 David 3 2012-08-31 Yes 232 312 623898923232123332 运营 [email protected] 6 True NULL
94 GC0001 David 6 2012-08-15 Yes 32 32 623898923232123332 运营 [email protected] 6 True NULL
95 GC0001 David 5 2012-08-22 No 323 32 623898923232123332 运营 [email protected] 6 True NULL
96 GC0001 David 2 2012-08-14 Yes 3443 4343 623898923232123332 运营 [email protected] 6 True NULL
97 GC0001 David 9 2012-08-14 Yes 43 4343 623898923232123332 运营 [email protected] 6 True NULL我用的SQL级联,就是统计不出合计来,帮我下,要用SQL语法统计的,其他方法不准用的
MAX(CASE [Month] WHEN 1 THEN Num ELSE 0 END) AS 一月,
MAX(CASE [Month] WHEN 2 THEN Num ELSE 0 END) AS 二月,
MAX(CASE [Month] WHEN 3 THEN Num ELSE 0 END) AS 三月,
MAX(CASE [Month] WHEN 4 THEN Num ELSE 0 END) AS 四月,
MAX(CASE [Month] WHEN 5 THEN Num ELSE 0 END) AS 五月,
MAX(CASE [Month] WHEN 6 THEN Num ELSE 0 END) AS 六月,
MAX(CASE [Month] WHEN 7 THEN Num ELSE 0 END) AS 七月,
MAX(CASE [Month] WHEN 8 THEN Num ELSE 0 END) AS 八月,
MAX(CASE [Month] WHEN 9 THEN Num ELSE 0 END) AS 九月,
MAX(CASE [Month] WHEN 10 THEN Num ELSE 0 END) AS 十月,
MAX(CASE [Month] WHEN 11 THEN Num ELSE 0 END) AS 十一月,
MAX(CASE [Month] WHEN 12 THEN Num ELSE 0 END) AS 十二月
FROM (
SELECT SUM(CAST(e.Money AS Float)) AS Num, FK.Kind, MONTH(e.Date) AS Month
FROM dbo.Expense AS e LEFT OUTER JOIN
dbo.BKind AS k ON e.KindID = k.ID LEFT OUTER JOIN
dbo.BKind AS FK ON k.FID = FK.ID
WHERE (YEAR(e.Date) =@Year) AND (FK.ID =0)
GROUP BY FK.Kind, MONTH(e.Date)
) AS s
group by Kind
END这个存储过程统计不到差旅费,我要的是一个总的合计,横排12个月12个合计的。数据表 Kind表1 差旅费 0
2 餐费 0
3 办公费 0
4 通讯费 0
5 其他 0
6 火车票 1
7 汽车票 1
8 出租车票 1
9 飞机票 1
10 住宿费 0
0 合计 2
表Month
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12报销表 Expense
93 GC0001 David 3 2012-08-31 Yes 232 312 623898923232123332 运营 [email protected] 6 True NULL
94 GC0001 David 6 2012-08-15 Yes 32 32 623898923232123332 运营 [email protected] 6 True NULL
95 GC0001 David 5 2012-08-22 No 323 32 623898923232123332 运营 [email protected] 6 True NULL
96 GC0001 David 2 2012-08-14 Yes 3443 4343 623898923232123332 运营 [email protected] 6 True NULL
97 GC0001 David 9 2012-08-14 Yes 43 4343 623898923232123332 运营 [email protected] 6 True NULL我用的SQL级联,就是统计不出合计来,帮我下,要用SQL语法统计的,其他方法不准用的
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货