求不同時間段匯總的優化算法!
現有表tb1
itemno date Qty
a 20050601 30
b 20050602 40
c 20060601 20
a 20060701 30
b 20070504 50
......................
求各個產品不同時間段的sum(Qty)
假如是要 求 200506 ,2006,2007 的 sum(Qty)
現在的算法是
create function dbo.fn_test(@itemno varchar(10),@iFlag int)
returns FLOAT --@iFlag = 1 200506 的sum 2 2006 3 2007
as
begin
declare @fValue float
select @fValue = sum(Qty) from tb1
where itemno = @itemno
and left(date,6) = case @iFlag when 1 then '200506 '
else left(date,6) end
and left(date,4) = case @iFlag when 2 then '2006 '
when 3 then '2007 '
else left(date,4) end
return @fValue
end
go
select itemno,Qty1 = dbo.fn_test(itemno,1)
,Qty2 = dbo.fn_test(itemno,2)
,Qty3 = dbo.fn_test(itemno,3)
from tb1 group by itemno
但是在實際過程中,數據要多的多,要複雜的多,這種算法就顯得比較慢.
請問有沒有其他好的算法?
------解决方案--------------------select tb1.itemno,tb2.date,sum(tb1.Qty) Qty from tb2,tb1 where charindex(tb2.date,tb1.date) > 0
group by itemno,date