急!急!急!求一个SQL写法的思路

[MagicJohn]

http://expert.csdn.net/Expert/TopicView1.asp?id=1915186

求满足条件的各个brand 按日期倒序的头三个费用的平均值
表结构如下

ROLL_COSTING
MONTH DATETIME PRIMARY KEY
BRAND VARCHAR PRIMARY KEY
SPEC_NO VARCHAR
COST INT

希望有一个用一个SQL写出来的解决方案. .....

[liuyun2003]

什么意思啊？？日期倒序到是可以理解。那么头三个费用的平均植是什么意思啊？

[rs_net]

select avg(cost) from (select top 3 * from roll_costing order by brand DESC)

[fanmb]

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 cur1

select brand,sum(cost)/count(brand) as cost from #fmb1
group by brand


drop table #fmb1

[nik_Amis]

up

[rs_net]

对不起，前面表达错了，你可以写存储过程如下：
CREATE PROCEDURE [aaa] AS
select top 3 * into #a from roll_costing order by brand desc

select avg(cost) from #a

[97866]

select Top 3 avg(cost) from roll_costing where 条件 Group by NOMTH order by NOMTH DESC

[happydreamer]

到底什么意思呢，我理解的不对么？

[MagicJohn]

你理解的应该没有错.

测试出的结果还是不对

这个
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是会有问题

[czsoft]

select avg(cost) from (select top 3 cost from roll_costing order by MONTH DESC)

[MagicJohn]

问题解决了>>>>>多谢!!!!

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