有一个复杂的查询,用来统计租用情况,按日期排序后,上下行之间的差(包括数量(surplus)和天数(uday))乘积之和,得到最终结果,现在代码写出来的,却有个诡异的问题,
select * from ts where productname='钢管' ,结果正确
select counts from ts where productname='钢管' 结果不正确了,经排除法发现问题在于subtotal这列,也就是select subtotal, counts from ts where productname='钢管',它就是正确的,去掉这列就不行,有基情?
0.60 1.20
0.90 -0.60
3.60 6.60
9.60 25.80
9.60 45.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='钢管'
select * from ts where productname='钢管' ,结果正确
select counts from ts where productname='钢管' 结果不正确了,经排除法发现问题在于subtotal这列,也就是select subtotal, counts from ts where productname='钢管',它就是正确的,去掉这列就不行,有基情?
0.60 1.20
0.90 -0.60
3.60 6.60
9.60 25.80
9.60 45.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='钢管'
ts里面的数据如果是固定的,列名应该不会影响其他列的呀...
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
不知为何多查询一列它就正常,少查询它就出错了
仔细对比了下,当选中subtotal时,rank是2,3,5,6,7;而没有它时,rank是2,3,4,5,6,为何出现这种情况呢?
select rank,id,projectid,projectName,productname,orderdirection,orderdate,subtotal
from t as b
)
select * from ts where productname='钢管'
学艺不精啊,是 Row_Number()出错了,select Row_Number() OVER ( ORDER by orderdate,productname ASC) rank,多加了个排序就正确了,只是心里觉得很奇怪,为何查询时 sum结果会影响到row_number的顺序?