--你的这个处理想不到什么好的优化办法,如果对于每个location,reasonno 的值唯一,试试下面的这种查询方法select a.location ,reasonno=isnull(b.reasonno,'other') ,defqty = sum(defqty) ,gs=case when b.reasonno is null then '2' else '1' end from table1 a left join( select location,reasonno from table1 a where ( select sum(1) from table1 where location=a.location and reasonno>=a.reasonno ) between 1 and 5 )b on a.location=b.location and a.reasonno=b.reasonno
,reasonno=isnull(b.reasonno,'other')
,defqty = sum(defqty)
,gs=case when b.reasonno is null then '2' else '1' end
from table1 a left join(
select location,reasonno from table1 a
where (
select sum(1) from table1
where location=a.location
and reasonno>=a.reasonno
) between 1 and 5
)b on a.location=b.location and a.reasonno=b.reasonno