Sql查询诡异的问题
有一个复杂的查询，用来统计租用情况，按日期排序后，上下行之间的差（包括数量(surplus)和天数(uday)）乘积之和，得到最终结果，现在代码写出来的，却有个诡异的问题，
select * from ts where productname='钢管' ，结果正确
select counts from ts where productname='钢管' 结果不正确了，经排除法发现问题在于subtotal这列，也就是select subtotal, counts from ts where productname='钢管'，它就是正确的，去掉这列就不行，有基情？
0.601.20
0.90-0.60
3.606.60
9.6025.80
9.6045.00
1.20
-0.60
25.80
45.00
45.00
declare @PID int,@PNE nvarchar(64);
set @pid=1;set @PNE='钢管'
declare @totollength decimal(18,2),@category varchar(64);
select @category=(case @PNE when '钢管' then 'Steel' when '扣件' then 'Fastening' when '套管' then 'CasingPipe' end);
with t as (
select Row_Number() OVER ( ORDER by orderdate ASC) rank, a.ID,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
) 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
)
,ts as (
select rank,id,projectid,projectName,productname,orderdirection,orderdate,subtotal,uday,surplus,uday * surplus as counts from (
select rank,id,projectid,projectName,productname,orderdirection,orderdate,subtotal,
(select DATEDIFF(d,(select OrderDate from t a where a.rank = b.rank )
,case when((select min(OrderDate) from t a where a.rank > b.rank and a.OrderDate<>b.OrderDate and a.productname=b.productname) is null) then getdate()
else (select min(OrderDate) from t a where a.rank>b.rank and a.OrderDate<>b.OrderDate and a.productname=b.productname) end)) as uday
,
isnull((select sum(isnull(SubTotal,0)) from t a where a.rank <= b.rank and OrderDirection = 0 and a.productname=b.productname),0)
-
isnull((select sum(isnull(SubTotal,0)) from t a where a.rank <= b.rank and OrderDirection = 1 and a.productname=b.productname),0)
as surplus
from t as b
) a
)
select counts from ts where productname='钢管'
[解决办法]
在with t那里，把里面的查询优化下，就几个表的连接查询，没必要弄这么多的子查询吧！！！然后试着看看结果。
