select * from (select *,row_number() over(partition by LOTID order by CleanTime) 'rn' from [表名] ) t where rn=1
with cte as( select row_number()over(partition by LOTID order by getdate())as xh ,.... ) select * from cte where xh=1
我这个是选出最大的cleantimeSELECT * FROM tab a WHERE EXISTS ( SELECT 1 FROM ( SELECT lotid , MAX(cleantime) cleantime FROM tab GROUP BY lotid ) b WHERE a.lotid = b.lotid AND a.cleantime = b.cleantime )
用row_number函数就可以了哈,非常简洁:select * from ( select *, row_number() over(partition by LOTID order by CleanTime desc) as rownum from tb ) t where rownum=1
select * from
(select *,row_number() over(partition by LOTID order by CleanTime) 'rn'
from [表名]
) t where rn=1
select row_number()over(partition by LOTID order by getdate())as xh
,....
)
select * from cte where xh=1
FROM tab a
WHERE EXISTS ( SELECT 1
FROM ( SELECT lotid ,
MAX(cleantime) cleantime
FROM tab
GROUP BY lotid
) b
WHERE a.lotid = b.lotid
AND a.cleantime = b.cleantime )
from
(
select *,
row_number() over(partition by LOTID order by CleanTime desc) as rownum
from tb
) t
where rownum=1