declare @brand varchar(12) declare cur1 cursor for select distinct brand from roll_costing create #fmb1 (month datetime null,brand varchar(20) null,spec_no varchar(12), cost integer null) open cur1 fetch cur1 into @brand while @@fetch_status = 0 begin insert #fmb1 (month,brand,spec_no,cost) select top 3 month,brand,spec_no,cost from roll_costing group by brand order by month desc where brand = @brand fetch cur1 into @brand end close cur1 deallocate cur1select brand,sum(cost)/count(brand) as cost from #fmb1 group by brand drop table #fmb1
对不起,前面表达错了,你可以写存储过程如下: CREATE PROCEDURE [aaa] AS select top 3 * into #a from roll_costing order by brand desc select avg(cost) from #a
select Top 3 avg(cost) from roll_costing where 条件 Group by NOMTH order by NOMTH DESC
你理解的应该没有错.测试出的结果还是不对这个 COST in (select top 3 cost from ROLL_COSTING where BRAND=a.BRAND and spec_no LIKE 'W%' and MONTH <= '2002/05/01' order by month desc ) 条件是有问题的, 比如说不同的BRAND有相同的COST是会有问题
select avg(cost) from (select top 3 cost from roll_costing order by MONTH DESC)
问题解决了>>>>>多谢!!!!SELECT DISTINCT BRAND, (SELECT AVG(TAB.COST) FROM (SELECT TOP 3 COST FROM ROLL_COSTING SUB_RC WHERE MAIN_RC.BRAND = SUB_RC.BRAND order by MONTH DESC) TAB) FROM ROLL_COSTING MAIN_RC
declare cur1 cursor for select distinct brand from roll_costing
create #fmb1 (month datetime null,brand varchar(20) null,spec_no varchar(12),
cost integer null)
open cur1
fetch cur1 into @brand
while @@fetch_status = 0
begin
insert #fmb1 (month,brand,spec_no,cost)
select top 3 month,brand,spec_no,cost from roll_costing group by brand
order by month desc where brand = @brand
fetch cur1 into @brand
end
close cur1
deallocate cur1select brand,sum(cost)/count(brand) as cost from #fmb1
group by brand
drop table #fmb1
CREATE PROCEDURE [aaa] AS
select top 3 * into #a from roll_costing order by brand desc select avg(cost) from #a
COST in (select top 3 cost from ROLL_COSTING where BRAND=a.BRAND and spec_no LIKE 'W%' and MONTH <= '2002/05/01'
order by month desc )
条件是有问题的,
比如说不同的BRAND有相同的COST是会有问题
(SELECT AVG(TAB.COST) FROM
(SELECT TOP 3 COST FROM ROLL_COSTING SUB_RC
WHERE MAIN_RC.BRAND = SUB_RC.BRAND order by MONTH DESC) TAB)
FROM ROLL_COSTING MAIN_RC