财务分析表(sql 问题)
在项目中有一个这样的需求，类似于统计小张一家的一年的经济状况。
人物：小张，小张爸爸老张，妈妈老李。
假定每人月收入分为2部分：一般为工资收入，另一部分额外收入。
月支出分为2部分计算：工资收入支出金额，额外收入支出金额。
月税金分为2部分计算：工资缴纳税金，额外收入部分缴纳的税金额。
月利润为当月家庭实际进账：收入-支出-税金。
如下表格 只填1月数字为例。
|
家庭经济分析 |
1月 |
2月 |
3月 |
4月 |
5月 |
6月 |
7月 |
8月 |
9月 |
10月 |
11月 |
12月 | ||
|
收入 |
小张 |
工资收入 |
100 |
|
|
|
|
|
|
|
|
|
|
|
|
小张 |
额外收入 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
老张 |
工资收入 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
老张 |
额外收入 |
60 |
|
|
|
|
|
|
|
|
|
|
| |
|
老李 |
工资收入 |
100 |
|
|
|
|
|
|
|
|
|
|
| |
|
老李 |
额外收入 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
合计 收入 |
工资收入 |
200 |
|
|
|
|
|
|
|
|
|
|
| |
|
额外收入 |
60 |
|
|
|
|
|
|
|
|
|
|
| ||
|
支出 |
小张 |
工资支出 |
20 |
|
|
|
|
|
|
|
|
|
|
|
|
小张 |
额外支出 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
老张 |
工资支出 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
老张 |
额外支出 |
30 |
|
|
|
|
|
|
|
|
|
|
| |
|
老李 |
工资支出 |
20 |
|
|
|
|
|
|
|
|
|
|
| |
|
老李 |
额外支出 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
合计 支出 |
工资支出 |
70 |
|
|
|
|
|
|
|
|
|
|
| |
|
额外支出 |
|
|
|
|
|
|
|
|
|
|
|
| ||
|
税金 |
小张 |
工资税金 |
10 |
|
|
|
|
|
|
|
|
|
|
|
|
小张 |
额外税金 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
老张 |
工资税金 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
老张 |
额外税金 |
10 |
|
|
|
|
|
|
|
|
|
|
| |
|
老李 |
工资税金 |
10 |
|
|
|
|
|
|
|
|
|
|
| |
|
老李 |
额外税金 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
合计 税金 |
工资税金 |
30 |
|
|
|
|
|
|
|
|
|
|
| |
|
额外税金 |
|
|
|
|
|
|
|
|
|
|
|
| ||
|
利润 |
小张 |
工资利润 |
70 |
|
|
|
|
|
|
|
|
|
|
|
|
小张 |
额外利润 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
老张 |
工资利润 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
老张 |
额外利润 |
20 |
|
|
|
|
|
|
|
|
|
|
| |
|
老李 |
工资利润 |
70 |
|
|
|
|
|
|
|
|
|
|
| |
|
老李 |
额外利润 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
合计 利润 |
工资利润 |
100 |
|
|
|
|
|
|
|
|
|
|
| |
|
额外利润 |
|
|
|
|
|
|
|
|
|
|
|
| ||
数据库部分设计为
创建table Budget（Name, type, Jan, Feb ,Mar ,Apr ,May ,Jun ,Jul ,Aug ,Sept , Oct ,Nov , Dece,CostRate,TaxRate）
其中costRate 为支出率，taxRate为税金率
创建一存储过程：成本利润统计
(
@Rate VarChar(50)
)
as
BEGIN
declare @whereStr VarChar(8000)
set @whereStr=' '
select @whereStr=
'SELECT Name, type,SUM(Jan* '+@Rate+') as Jan, SUM(Feb* '+@Rate+') as Feb, SUM(Mar* '+@Rate+') as Mar, SUM(Apr* '+@Rate+') as Apr, SUM(May* '+@Rate+') as May, SUM(Jun* '+@Rate+') as Jun, SUM(Jul* '+@Rate+') as Jul, SUM(Aug* '+@Rate+') as Aug, SUM(Sept* '+@Rate+') as Sept, SUM(Oct* '+@Rate+') as Oct, SUM(Nov* '+@Rate+') as Nov, SUM(Dece* '+@Rate+') as Dece
FROM Budget
GROUP BY Name, type'
EXEC (@whereStr)
END
计算收入情况时，@Rate=1
计算支出情况时，@Rate=CostRate
计算税金情况时，@Rate=taxRate
计算利润情况时，@Rate=1-CostRate-taxRate
根据四种情况分别执行此存储过程可以得到类似于下图黑色部分内容。但是需求还要有黄色合计部分。
|
收入 |
小张 |
工资收入 |
100 |
|
|
|
|
|
|
|
|
|
|
|
|
小张 |
额外收入 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
老张 |
工资收入 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
老张 |
额外收入 |
60 |
|
|
|
|
|
|
|
|
|
|
| |
|
老李 |
工资收入 |
100 |
|
|
|
|
|
|
|
|
|
|
| |
|
老李 |
额外收入 |
|
|
|
|
|
|
|
|
|
|
|
| |
|
合计 收入 |
工资收入 |
200 |
|
|
|
|
|
|
|
|
|
|
| |
|
额外收入 |
60 |
|
|
|
|
|
|
|
|
|
|
|
我用了一个比较笨的方式进行处理的，再建一存储过程：建一临时表，调用收入的存储过程，将结果放入临时表，统计出收入的合计。再将结果放入此临时表，依次筛出支出，税金，利润的结果放入临时表。
AS
begin
if object_id('Tempdb..#T') is not null
drop table #T
create table #T
(
id int identity(1,1) primary key not null
,name varchar(20),type varchar(20), Jan decimal(14,4), Feb decimal(14,4),Mar decimal(14,4)
,Apr decimal(14,4),May decimal(14,4),Jun decimal(14,4),Jul decimal(14,4),Aug decimal(14,4)
,Sept decimal(14,4), Oct decimal(14,4),Nov decimal(14,4), Dece decimal(14,4)
)
select * into #T1 from #T
insert #T exec gt_CostPerfit 1 ---收入计算
insert #T1 exec gt_CostPerfit 1
Insert into #T(type ,Jan,Feb,Mar,Apr,May,Jun,Jul, Aug,Sept,Oct,Nov,Dece) SELECT type , SUM(Jan) AS Jan,SUM(Feb) AS Feb,SUM(Mar) AS Mar,SUM(Apr) AS Apr,SUM(May) AS May,SUM(Jun) AS Jun,SUM(Jul) AS Jul,SUM(Aug) AS Aug,SUM(Sept) AS Sept,SUM(Oct) AS Oct,SUM(Nov) AS Nov,SUM(Dece) AS Dece
FROM #T1
GROUP BY name ---收入合计
delete #T1
insert #T exec gt_CostPerfit 'CostRate/100' ---成本计算
insert #T1 exec gt_CostPerfit 'CostRate/100'
Insert into #T(type ,Jan,Feb,Mar,Apr,May,Jun,Jul, Aug,Sept,Oct,Nov,Dece) SELECT type , SUM(Jan) AS Jan,SUM(Feb) AS Feb,SUM(Mar) AS Mar,SUM(Apr) AS Apr,SUM(May) AS May,SUM(Jun) AS Jun,SUM(Jul) AS Jul,SUM(Aug) AS Aug,SUM(Sept) AS Sept,SUM(Oct) AS Oct,SUM(Nov) AS Nov,SUM(Dece) AS Dece
FROM #T1
GROUP BY name ---成本合计
delete #T1
insert #T exec gt_CostPerfit 'TaxRate/100' ---税金计算
insert #T1 exec gt_CostPerfit 'TaxRate/100'
Insert into #T(type ,Jan,Feb,Mar,Apr,May,Jun,Jul, Aug,Sept,Oct,Nov,Dece) SELECT type , SUM(Jan) AS Jan,SUM(Feb) AS Feb,SUM(Mar) AS Mar,SUM(Apr) AS Apr,SUM(May) AS May,SUM(Jun) AS Jun,SUM(Jul) AS Jul,SUM(Aug) AS Aug,SUM(Sept) AS Sept,SUM(Oct) AS Oct,SUM(Nov) AS Nov,SUM(Dece) AS Dece
FROM #T1
GROUP BY name ---税金合计
delete #T1
insert #T exec gt_CostPerfit '(100-CostRate-TaxRate)/100' ---利润计算
insert #T1 exec gt_CostPerfit '(100-CostRate-TaxRate)/100'
Insert into #T(type ,Jan,Feb,Mar,Apr,May,Jun,Jul, Aug,Sept,Oct,Nov,Dece) SELECT type , SUM(Jan) AS Jan,SUM(Feb) AS Feb,SUM(Mar) AS Mar,SUM(Apr) AS Apr,SUM(May) AS May,SUM(Jun) AS Jun,SUM(Jul) AS Jul,SUM(Aug) AS Aug,SUM(Sept) AS Sept,SUM(Oct) AS Oct,SUM(Nov) AS Nov,SUM(Dece) AS Dece
FROM #T1
GROUP BY name ---利润合计
delete #T1
select * from #T
End
用这2个存储过程功能可以实现，但是效率比较低。目前我还没想到更好的解决方式，希望有此经验的人给提点建议。
