求不同段的化算法!
有表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