with tb(num,明细项,收入,支出,余额,出占总支出比)as(
select 1,'test',0.00,140.00,0,'63.63%' union
select 2,'test',1000.00,60.00,0,'27.27%' union
select 3,'邮费',0.00,20.00,0,'9.09%')
select 明细项,收入,支出,(select SUM(收入+支出*-1)
from tb where a.num>=num),出占总支出比
from tb a
调试欢乐多
这个数据是通过视图得到的,如果这样改的话,视图没法整合啊。视图是这样写的
SELECT dbo.Detail.id AS 编号, dbo.DetailData.jinfeibiaoId AS 经费表编号, dbo.Detail.title AS 明细项,
dbo.DetailData.Abstract AS 摘要, CONVERT(nvarchar, CAST(dbo.DetailData.income AS money), 1) AS 收入,
CONVERT(nvarchar, CAST(dbo.DetailData.pay AS money), 1) AS 支付, CONVERT(nvarchar,
CAST(dbo.DetailData.income - dbo.DetailData.pay AS money), 1) AS 余额,
{ fn CONCAT(ROUND(dbo.DetailData.pay /
(SELECT 总支出
FROM dbo.v_sumPay
WHERE (jinfeibiaoId = dbo.DetailData.jinfeibiaoId)) * 100, 2), '%') } AS 支出占总支出比
FROM dbo.DetailData INNER JOIN
dbo.Detail ON dbo.Detail.id = dbo.DetailData.DetailId具体实现的话,应该是一个怎么样的思路?求教。
这个数据是通过视图得到的,如果这样改的话,视图没法整合啊。视图是这样写的
SELECT dbo.Detail.id AS 编号, dbo.DetailData.jinfeibiaoId AS 经费表编号, dbo.Detail.title AS 明细项,
dbo.DetailData.Abstract AS 摘要, CONVERT(nvarchar, CAST(dbo.DetailData.income AS money), 1) AS 收入,
CONVERT(nvarchar, CAST(dbo.DetailData.pay AS money), 1) AS 支付, CONVERT(nvarchar,
CAST(dbo.DetailData.income - dbo.DetailData.pay AS money), 1) AS 余额,
{ fn CONCAT(ROUND(dbo.DetailData.pay /
(SELECT 总支出
FROM dbo.v_sumPay
WHERE (jinfeibiaoId = dbo.DetailData.jinfeibiaoId)) * 100, 2), '%') } AS 支出占总支出比
FROM dbo.DetailData INNER JOIN
dbo.Detail ON dbo.Detail.id = dbo.DetailData.DetailId具体实现的话,应该是一个怎么样的思路?求教。就是一个子查询
--sql2012--数据
明细项 收入 支出 余额 支出占总支出比
test 0.00 140.00 -140.00 63.63%
test 1000.00 60.00 800.00 27.27%
邮费 0.00 20.00 780.00 9.09%--创建表#a,并输入明细项,收入,指出三列
--余额,占比两列视为报表
drop table #a
create table #a
(
CostName varchar(20),
GetMoney decimal(9,2),
CostMoney decimal(9,2)
)
insert into #a (CostName,GetMoney,CostMoney)
values('test1',0.00,140.00),
('test2',1000.00,60.00),
('邮费',0.00,20.00)
select* from #a --结果展示
/*
CostName GetMoney CostMoney
-------------------- --------------------------------------- ---------------------------------------
test1 0.00 140.00
test1 1000.00 60.00
邮费 0.00 20.00(3 行受影响)
*/
--实现余额
;with a as
(
select costname,getmoney,costmoney,getmoney - costmoney as cash,row_number() over( order by costname) as rn
from #a
)
select costname,getmoney,costmoney
,cash1=(select sum(cash) from a as b where a.rn>=b.rn) --余额
from a
--结果展示
/*
costname getmoney costmoney cash1
-------------------- --------------------------------------- --------------------------------------- ---------------------------------------
test1 0.00 140.00 -140.00
test2 1000.00 60.00 800.00
邮费 0.00 20.00 780.00(3 行受影响)*/
--实现占比
;with a as
(
select costname,getmoney,costmoney,getmoney - costmoney as cash,row_number() over( order by costname) as rn
from #a
)
select costname,getmoney,costmoney
,cash1=(select sum(cash) from a as b where a.rn>=b.rn) --余额
,cast(cast(costmoney*1.0/sum(costmoney) over()*100 as decimal(9,2))as varchar) +'%' as costpercent--占比 /*声明,该语句仅能在sql2012以及以上版本使用,如果博主有自己的方法,可去掉该语句,即可在sql2005以及以上版本使用*/
from a
--结果展示
/*
costname getmoney costmoney cash1 costpercent
-------------------- --------------------------------------- --------------------------------------- --------------------------------------- -------------------------------
test1 0.00 140.00 -140.00 63.64%
test2 1000.00 60.00 800.00 27.27%
邮费 0.00 20.00 780.00 9.09%(3 行受影响)*/