最近做的一个小项目，出租管理，需要计算租金，有点复杂，如下图

 

经过研究，写出代码如下：

set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER proc [dbo].[Expensedetails]        @PID int,        @PNE nvarchar(64)as--declare @Temp_A table(rank int null,projectID int null,ProjectName nvarchar(200) null,ProductName nvarchar(64) null,OrderDirection int null,orderdate smalldatetime null,subtotal decimal(18,2) null);--declare @Temp_B table(rank int null,projectID int null,ProjectName nvarchar(200) null,ProductName nvarchar(64) null,OrderDirection int null,orderdate smalldatetime null,subtotal decimal(18,2) null, uday decimal(18,2) null,surplus decimal(18,2) null,counts decimal(18,2) null);with t as (select Row_Number() OVER ( ORDER by orderdate ASC) rank, a.ProjectID,a.ProjectName,a.ProductName,a.OrderDirection,a.OrderDate,sum(a.SubTotal) as SubTotal from (select a.ID,a.ProjectID,b.ProjectName,a.ProductName,a.OrderDirection,a.OrderDate,a.SubTotal from (select a.ID,b.ProductName,a.ProjectID,a.OrderDirection,a.OrderDate,a.SubTotal from (select a.ID,a.ProjectID,b.StandardID,a.OrderDirection,a.OrderDate,b.SubTotal from SteelBusinessOrder ajoin (select StandardID,sum(SubTotal) as SubTotal,OrderID from SteelBusinessList group by StandardID,OrderID) bon a.ID = b.OrderID) ajoin SteelStandard bon a.StandardID = b.ID and b.ProductName = @PNE) ajoin SteelProject bon a.ProjectID = b.ID and b.ID = @PID) a group by a.ID,a.ProjectID,a.ProjectName,a.ProductName,a.OrderDirection,a.OrderDate)--insert into @Temp_A select * from t;with ts as (select *,uday * surplus as counts from (select  *,(select DATEDIFF(d,(select OrderDate from @Temp_A a where a.rank = b.rank),case when((select  min(OrderDate) from @Temp_A a where a.rank > b.rank ) is null) then dateadd(d,1,getdate())else (select  min(OrderDate) from @Temp_A a where a.rank>b.rank ) end)) as uday,isnull(isnull((select sum(isnull(SubTotal,0)) from @Temp_A a where a.rank <= b.rank and OrderDirection = 0 ),0)-isnull((select sum(isnull(SubTotal,0)) from @Temp_A a where a.rank <= b.rank and OrderDirection = 1 ),0),0)as surplusfrom @Temp_A as b) a)--insert into @Temp_B select * from tsselect rank,projectID,ProjectName,ProductName,OrderDirection,convert(char(10),orderdate,120) as orderdate,subtotal, uday,surplus,counts,(select SUM(counts) as zsum from @Temp_B  a where a.rank < = b.rank) as zsum,(case OrderDirection when 0 then '出库' when 1 then '入库' end) as Order_Direction   from @Temp_B  b

刚开始还行，数据一多就开始慢，29行数据达到了1.5秒，在网上问了几天也没有结果，自己在无意中看了下执行计划，只觉得自己的屏幕太小，非常复杂，后来看到是最后那个数据zsum有点问题，去掉了速度还可以，在网上又找了找，看到了表变量，果断在每个查询后面都以表变量存起来再用，就是上例中注释的部分，再执行，只有35ms，太好了~