表名: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條和最後的兩條取消,不計算.如上(//取消)請高手賜教。
數據集為:
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條和最後的兩條取消,不計算.如上(//取消)請高手賜教。
mold Jibie Part PName RTime
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 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
數據集再avg匯總求平均值
from (select * from (select * from menu where not exists(select top 2 * from menu group by pname order by rtime))a where not exists(select top 2 * from menu group by pname order by rtime desc))b
group by Mold, Jibie,Part,PName
order by Mold, Jibie,Part,PName
from (select * from (select * from menu where not exists(select top 2 * from menu group by pname order by rtime))a where not exists(select top 2 * from menu group by pname order by rtime desc))b
group by Mold, Jibie,Part,PName
order by Mold, Jibie,Part,PName
SELECT Mold, Jibie,Part,PName,AVGRealTime = AVG(RTime)
from menu where rtime not in((select top 2 rtime from menu group by pname order by rtime) union all
(select top 2 rtime from menu group by pname order by rtime desc))
沒有匯總函數 group by 不能跑
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', 9select 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)
go insert into testtable
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 a.mold, a.Jibie,a.Part,a.PName,avg(a.RTime)
from testtable a left join
(
select a.mold, a.Jibie,a.Part,a.PName,max(RTime) maxRTime,min(RTime) minRTime from
testtable a left join
(select mold, Jibie,Part,PName,max(RTime) maxRTime,min(RTime) minRTime from testtable
group by mold, Jibie,Part,PName
) b on a.mold=b.mold and a.Jibie=b.Jibie and a.Part=b.Part and a.PName=b.PName
where a.RTime>b.minRTime and a.RTime<b.maxRTime
group by a.mold, a.Jibie,a.Part,a.PName
) b on a.mold=b.mold and a.Jibie=b.Jibie and a.Part=b.Part and a.PName=b.PName
where a.RTime>b.minRTime and a.RTime<b.maxRTime
group by a.mold, a.Jibie,a.Part,a.PName
---- ----- ---- ----- -----------
MO A 101 AS 6
MO A 101 FG 4
declare @a table(mold varchar(10),Jibie varchar(10),Part int,PName varchar(10),RTime int)
insert into @a
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 Mold, Jibie,Part,PName,AVG(RTime) as rtime from @a a where rtime not in(select top 2 rtime from @a b where b.pname=a.pname order by rtime desc)
and rtime not in (select top 2 rtime from @a b where b.pname=a.pname order by rtime )
group by Mold, Jibie,Part,PName
order by Mold, Jibie,Part,PName
result:
Mold Jibie Part PName rtime
---------- ---------- ----------- ---------- -----------
MO A 101 AS 6
MO A 101 FG 4(所影响的行数为 2 行)