SQL 月中统计

这几天弄一个牵涉到收房租的项目，需要统计房租收入、支出和退款情况，即每年每个月的这三种情况。

 

要求：

1、三种情况弄在一个页面中，第一列月份、第二列收入情况、第三列支出情况、第四列退款；

2、并不是显示12个月的数据，而是哪个月份有数据就显示哪个月的；

3、最后一行为合计列，显示三种情况的各个合计。

 

三种情况对应的表如下：

收入：table2 收款金额：field14 收款日期：field16

支出：table4 支出金额：field4   支出日期：field3

退款：table3 退款金额：field14 退款日期：field15

具体情况如图：

这里的处理方式是：用SQL写出个视图，然后直接从视图中取数据。SQL如下：

 

CREATE VIEW [dbo].[incomeOrOutView]
AS
SELECT  CAST(aa.f1 AS varchar(10)) AS field1, CAST(aa.f2 AS varchar(10)) AS field2, 
ISNULL(bb.f1, 0) AS field3, ISNULL(cc.f1, 0) AS field4, ISNULL(dd.f1, 0) AS field5
FROM     (SELECT DISTINCT YEAR(f1) AS f1, MONTH(f1) AS f2
                FROM     (SELECT  field16 AS f1 FROM dbo.table2
                                UNION ALL
                                SELECT  field3 AS f1 FROM dbo.table4
                                WHERE  (field5 = '完成')
                                UNION ALL
                                SELECT  field15 AS f1 FROM     dbo.table3)                         
						  AS ee) AS aa LEFT OUTER JOIN
                   (SELECT  SUM(field14) AS f1, YEAR(field16) AS f2, MONTH(field16) AS f3
                    FROM     dbo.table2 AS table4_1
                    GROUP BY YEAR(field16), MONTH(field16)) AS bb 
                    ON aa.f1 = bb.f2 AND aa.f2 = bb.f3 LEFT OUTER JOIN
                   (SELECT  SUM(field4) AS f1, YEAR(field3) AS f2, MONTH(field3) AS f3
                    FROM     dbo.table4 AS table5_1
                    WHERE  (field5 = '完成')
                    GROUP BY YEAR(field3), MONTH(field3)) AS cc 
                    ON aa.f1 = cc.f2 AND aa.f2 = cc.f3 LEFT OUTER JOIN
                   (SELECT  SUM(field14) AS f1, YEAR(field15) AS f2, MONTH(field15) AS f3
                    FROM     dbo.table3 AS table6_1
                    GROUP BY YEAR(field15), MONTH(field15)) AS dd 
                    ON aa.f1 = dd.f2 AND aa.f2 = dd.f3

 

 

 简单说明一下：

1、从table2、table3、table4三个表中日期，得到所有有记录的日期数据，作为表ee

2、对表ee取年份和月份，并去除冗余，得到所有记录中的有的年份和月份（满足要求中的第二条：哪个月份有数据就显示哪个月的），作为表aa

3、分别从table2、table3、table4三个表取相应数据并与表aa联结，联结条件为：年份和月份相同，得到基本数据结果。

 

最后添加过滤数据——只显示今年的数据，并添加合计列：

select * from incomeOrOutView
where
field1 = year(getdate()) 
UNION ALL 
SELECT  year(getdate()), '合计', ISNULL(sum(field3),0), 
ISNULL(sum(field4),0), ISNULL(sum(field5),0) 
FROM     incomeOrOutView 
WHERE  field1 = year(getdate())

 问题解决到这里，看起来应该是差不多了，若把另一种考量加进来，似乎也合情合理：

每个月交房租就一定是当月的1号-30号吗？，或者1-30号交的钱就一定是当前月的？我要是每个月15号过后就算下个月的呢？

对，后来我就碰到了这个问题，要求：上个月10到这个月9号，算这个月的。即：从2015-7-10到2015-8-9算8月份的。

有一个很简单的方法可以处理掉这个问题，就是在录数据的时候就标记下是收哪个月的，这样在统计的时候就可以直接取值而不需要特殊判断了。弄的系统中的确有这么一字段也是必填项，建议客户用这个，结果客户说：我这个数据是用来告诉我自己这个钱是交的几月份的，但是我做账还得用录入的时间。很无语，只能接着改。

 

改的思路是：先弄一个SQL，将所有的数据按照选定的日期拼接起来，在添加字段用来标记这条数据应该是哪个月的，这样到时候直接取值，具体SQL如下：

--1、创建视图用来拼接符合条件的数据
CREATE VIEW [dbo].[subInOrOutView]
AS
SELECT  field14 AS f1, field16 AS f2, YEAR(field16) AS f3, 
(CASE WHEN t2.field16 BETWEEN '' + CAST(year(t2.field16) AS varchar(50)) 
+ '-' + CAST((month(t2.field16)) AS varchar(50)) + '-10 00:00:00' 
AND '' + CAST(year(t2.field16) AS varchar(50)) + '-' + CAST(month(t2.field16) 
+ 1 AS varchar(50)) + '-9 23:59:59' 
THEN month(t2.field16) + 1 
ELSE month(t2.field16) END) AS f4, 
2 AS f5
FROM     dbo.table2 AS t2

UNION ALL
SELECT  field4 AS f1, field3 AS f2, YEAR(field3) AS f3, 
(CASE WHEN t4.field3 BETWEEN '' + CAST(year(t4.field3) AS varchar(50)) 
+ '-' + CAST((month(t4.field3)) AS varchar(50)) + '-10 00:00:00' 
AND '' + CAST(year(t4.field3) AS varchar(50)) 
+ '-' + CAST(month(t4.field3) + 1 AS varchar(50)) + '-9 23:59:59' 
THEN month(t4.field3) + 1 
ELSE month(t4.field3) END) AS f4, 
4 AS f5
FROM     dbo.table4 AS t4
WHERE  (field5 = '完成')

UNION ALL
SELECT  field14 AS f1, field15 AS f2, YEAR(field15) AS f3, 
(CASE WHEN t3.field15 BETWEEN '' + CAST(year(t3.field15) AS varchar(50)) 
+ '-' + CAST((month(t3.field15)) AS varchar(50)) + '-10 00:00:00' 
AND '' + CAST(year(t3.field15) AS varchar(50)) 
+ '-' + CAST(month(t3.field15) + 1 AS varchar(50)) + '-9 23:59:59' 
THEN month(t3.field15) + 1 
ELSE month(t3.field15) END) AS f4, 
3 AS f5
FROM     dbo.table3 AS t3

--2、修改原有视图
ALTER VIEW [dbo].[incomeOrOutView]
AS
SELECT  CAST(aa.f1 AS varchar(10)) AS field1, CAST(aa.f2 AS varchar(10)) AS field2, 
ISNULL(bb.f1, 0) AS field3, ISNULL(cc.f1, 0) AS field4, ISNULL(dd.f1, 0) AS field5
FROM     (SELECT DISTINCT f3 AS f1, f4 AS f2
                FROM     dbo.subInOrOutView) AS aa LEFT OUTER JOIN
                   (SELECT  SUM(f1) AS f1, f3 AS f2, f4 AS f3
                    FROM     dbo.subInOrOutView AS subInOrOutView_3
                    WHERE  (f5 = 2)
                    GROUP BY f3, f4) AS bb 
					ON aa.f1 = bb.f2 AND aa.f2 = bb.f3 LEFT OUTER JOIN
                   (SELECT  SUM(f1) AS f1, f3 AS f2, f4 AS f3
                    FROM     dbo.subInOrOutView AS subInOrOutView_2
                    WHERE  (f5 = 4)
                    GROUP BY f3, f4) AS cc 
					ON aa.f1 = cc.f2 AND aa.f2 = cc.f3 LEFT OUTER JOIN
                   (SELECT  SUM(f1) AS f1, f3 AS f2, f4 AS f3
                    FROM     dbo.subInOrOutView AS subInOrOutView_1
                    WHERE  (f5 = 3)
                    GROUP BY f3, f4) AS dd 
		    ON aa.f1 = dd.f2 AND aa.f2 = dd.f3

也简单说明一下：

1、先判断日期是否在指定的时间范围（这个月10号到下个月9号）之内，若是在那么月份就设置为下个月的，即当前月+1；否则就设置为当前月份；

2、添加字段用来区分这个数据是收入、支出还是退款，即f5所代表的2、4、3；

3、修改的视图中直接从筛选好的月份里面取数据并和其他数据联结查询得到最后结果；

 

若不想弄前面那个视图，还有一个更简单的，就是在每个表上加个触发器用来监听三个日期，每当日期有变化时，就计算一下，看看它是属于哪个月份的，最后做统计的时候直接取那个字段的月份就成。