select top 20 * (
select avg(a.ps1_qty),b.pro_code from p_sale a,product b where a.pro_id=b.pro_id group by b.pro_code)不好意思,我是新手,不知道对不对,还希望高手们找找毛病
select avg(a.ps1_qty),b.pro_code from p_sale a,product b where a.pro_id=b.pro_id group by b.pro_code)不好意思,我是新手,不知道对不对,还希望高手们找找毛病
总和用sum
平均用avg
select top 20 b.pro_code,sum(a.psl_qty)
from p_sale a, product b
where a.pro_id=b.pro_id
group by pro_id
order by sum(a.psl_qty) desc
select b.pro_code,a.pro_id,sum(a.psl_qty) as psl_qty into #t from p_sale a,product b
where a.pro_id=b.pro_id group by b.pro_code,a.pro_id
order by b.pro_codeselect * from #t a where (select count(*) from #t where pro_code=a.pro_code and psl_qty>=a.psl_qty) <= 20
order by pro_code, psl_qty descdrop table #t
或者将临时表建成一张视图调用都可以。
select * from (select b.pro_code,a.pro_id,sum(a.psl_qty) as psl_qty from p_sale a,product b
where a.pro_id=b.pro_id group by b.pro_code,a.pro_id) a where (select count(*) from (select b.pro_code,a.pro_id,sum(a.psl_qty) as psl_qty from p_sale a,product b
where a.pro_id=b.pro_id group by b.pro_code,a.pro_id) b where b.pro_code=a.pro_code and b.psl_qty>=a.psl_qty) <= 20
order by pro_code, psl_qty desc
select b.pro_code,a.pro_id,a.psl_qty
from
(select a.pro_id,a.sum(psl_qty) as psl_qty,b.pro_code
from p_sale a,product b
where a.pro_id=b.pro_id
group by a.pro_id,b.pro_code) a and a.pro_id in
(select top 20 pro_id from
(select a.pro_id,a.sum(psl_qty) as psl_qty,b.pro_code
from p_sale a,product b where a.pro_id=b.pro_id
group by a.pro_id,b.pro_code) where pro_code= a.pro_code
order by psl_qty desc)
order by a.pro_id desc,a.psl_qty desc
select b.pro_code,a.pro_id,a.psl_qty
from
(select a.pro_id,a.sum(psl_qty) as psl_qty,b.pro_code
from p_sale a,product b
where a.pro_id=b.pro_id
group by a.pro_id,b.pro_code) a where a.pro_id in
(select top 20 pro_id from
(select a.pro_id,a.sum(psl_qty) as psl_qty,b.pro_code
from p_sale a,product b where a.pro_id=b.pro_id
group by a.pro_id,b.pro_code) t where pro_code= a.pro_code
order by psl_qty desc)
order by a.pro_id desc,a.psl_qty desc