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

 

经过研究,写出代码如下:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER 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 a
join (select StandardID,sum(SubTotal) as SubTotal,OrderID from SteelBusinessList group by StandardID,OrderID) b
on a.ID = b.OrderID
) a
join SteelStandard b
on a.StandardID = b.ID and b.ProductName = @PNE
) a
join SteelProject b
on 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 surplus
from @Temp_A as b
) a
)
--insert into @Temp_B select * from ts
select 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,太好了~

posted on 2013-05-28 18:40  happydaily  阅读(1286)  评论(0编辑  收藏  举报