高手教查
表名：Manu
集：
mold Jibie Part PName RTime
MO A 101 AS 0　　　　//取消
MO A 101 AS 1　　　　//取消
MO A 101 AS 2
MO A 101 AS 3
MO A 101 AS 4
MO A 101 AS 5
MO A 101 AS 6
MO A 101 AS 7
MO A 101 AS 8
MO A 101 AS 9
MO A 101 AS 10
MO A 101 AS 11 //取消
MO A 101 AS 12 //取消
MO A 101 FG 0 //取消
MO A 101 FG 1 //取消
MO A 101 FG 2
MO A 101 FG 3
MO A 101 FG 4

MO A 101 FG 5
MO A 101 FG 6
MO A 101 FG 7
MO A 101 FG 8 //取消
MO A 101 FG 9 //取消

此工，在算Realtime的平均值　
SELECT Mold, Jibie,Part,PName,AVGRealTime = AVG(RealTime)
from Manu
group by Mold, Jibie,Part,PName
order by Mold, Jibie,Part,PName


在要取各不同Mold, Jibie,Part,PName　工的，最小的2和最後的取消，不算.如上(//取消)高手教。


[解决办法]
select * from (select * from tablename where not exists(select top 2 * from tablename group by pname order by rtime))a where not exists(select top 2 * from tablename group by pname order by rtime desc)
[解决办法]
create table Manu(mold varchar(10), Jibie varchar(10), Part varchar(10), PName varchar(10), RTime int)
insert Manu select 'MO ', 'A ', '101 ', 'AS ', 0
union all select 'MO ', 'A ', '101 ', 'AS ', 1
union all select 'MO ', 'A ', '101 ', 'AS ', 2
union all select 'MO ', 'A ', '101 ', 'AS ', 3
union all select 'MO ', 'A ', '101 ', 'AS ', 4
union all select 'MO ', 'A ', '101 ', 'AS ', 5
union all select 'MO ', 'A ', '101 ', 'AS ', 6
union all select 'MO ', 'A ', '101 ', 'AS ', 7
union all select 'MO ', 'A ', '101 ', 'AS ', 8
union all select 'MO ', 'A ', '101 ', 'AS ', 9
union all select 'MO ', 'A ', '101 ', 'AS ' , 10
union all select 'MO ', 'A ', '101 ', 'AS ', 11
union all select 'MO ', 'A ', '101 ', 'AS ', 12
union all select 'MO ', 'A ', '101 ', 'FG ', 0
union all select 'MO ', 'A ', '101 ' , 'FG ', 1
union all select 'MO ', 'A ', '101 ', 'FG ', 2
union all select 'MO ', 'A ', '101 ', 'FG ', 3
union all select 'MO ', 'A ', '101 ', 'FG ', 4
union all select 'MO ', 'A ', '101 ', 'FG ', 5

union all select 'MO ', 'A ', '101 ', 'FG ', 6
union all select 'MO ', 'A ', '101 ', 'FG ', 7
union all select 'MO ', 'A ', '101 ', 'FG ', 8
union all select 'MO ', 'A ', '101 ', 'FG ', 9

select Manu.Mold, Manu.Jibie, Manu.Part, Manu.PName, AVGRealTime=AVG(Manu.RTime) from Manu
left join
(
select * from Manu as A
where (select count(*) from Manu where Mold=A.Mold and Jibie=A.Jibie and Part=A.Part and PName=A.PName and RTime <A.RTime) <2
or (select count(*) from Manu where Mold=A.Mold and Jibie=A.Jibie and Part=A.Part and PName=A.PName and RTime> A.RTime) <2
)A on Manu.Mold=A.Mold and Manu.Jibie=A.Jibie and Manu.Part=A.Part and Manu.PName=A.PName and Manu.RTime=A.RTime
where A.Mold is null
group by Manu.Mold, Manu.Jibie, Manu.Part, Manu.PName

--result
Mold Jibie Part PName AVGRealTime
---------- ---------- ---------- ---------- -----------
MO A 101 AS 6
MO A 101 FG 4

(2 row(s) affected)