請高手賜教數據查詢問題
表名：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)
