下图为使用select 选择后的数据,想对其按照Lotid进行分组,并取最大的cleantimes的一笔数据。
sql语句:
SELECT w.WORKORDER,p.NAME,p.WAFERSIZE, w.LastProcessEqp ,lh.LOTID,w.CleanTimes,lh.QTY
FROM lothistory LH,allwafer w,product p,tool t,workorder wo
WHERE lh.TOOLID=t.id AND lh.LOTID=w.LOTID AND wo.workorderid=w.workorder AND wo.productname=p.name
AND LH.LOTDATETIME >= '2013-10-14 00:00:00.000' AND LH.LOTDATETIME <= '2013-10-15 00:00:00.000' AND w.CleanTimes >=3 and LH.QTY>0 and t.Name like 'fc%' and LH.ACTION='MoveOut'
GROUP BY w.WORKORDER,p.NAME,p.WAFERSIZE, w.LastProcessEqp,lh.LOTID,w.CleanTimes,lh.QTY
结果:
![](http://img.bbs.csdn.net/upload/201310/21/1382322161_288767.jpg)
使用row_number()基本用法:
select *
from
(
select *,
row_number() over(partition by LOTID order by CleanTime desc) as rownum
from tb
) t
where rownum=1
最终代码代码:
select WORKORDER,NAME,WAFERSIZE, LastProcessEqp ,LOTID,CleanTimes,QTY
from
(
select *,
row_number() over(partition by LOTID order by CleanTimes desc) as rownum
from (SELECT w.WORKORDER,p.NAME,p.WAFERSIZE, w.LastProcessEqp ,lh.LOTID,w.CleanTimes,lh.QTY
FROM lothistory LH,allwafer w,product p,tool t,workorder wo
WHERE lh.TOOLID=t.id AND lh.LOTID=w.LOTID AND wo.workorderid=w.workorder AND wo.productname=p.name
AND LH.LOTDATETIME >= '2013-10-14 00:00:00.000' AND LH.LOTDATETIME <= '2013-10-15 00:00:00.000' AND w.CleanTimes >=3 and LH.QTY>0 and t.Name like 'fc%' and LH.ACTION='MoveOut'
GROUP BY w.WORKORDER,p.NAME,p.WAFERSIZE, w.LastProcessEqp,lh.LOTID,w.CleanTimes,lh.QTY
)bbb
) as tg
where rownum=1