如题,方法很简单,只要一句话就可以解决:
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY LineCode ORDER BY OnTime DESC) NewIndex
FROM dbo.TPBOM WHERE LineCode IN
(SELECT LineCode FROM dbo.TShop) AND A.OnTime IS NOT NULL
) D WHERE D.NewIndex = 1
----------------------------------------------------------------------------------------------------------
执行
SELECT *, ROW_NUMBER() OVER (PARTITION BY LineCode ORDER BY OnTime DESC) NewIndex
FROM dbo.TPBOM WHERE LineCode IN
(SELECT LineCode FROM dbo.TShop) AND A.OnTime IS NOT NULL
得到
结果集
FID Barcode OnTime LineCode NewIndex
112784446332100010e 2013-10-14 13:25:00 L2 1
112784434332100010r 2013-10-14 13:13:30 L2 2
112784465332100010t 2013-10-14 12:13:00 L2 3
112784466332100010y 2013-10-14 11:25:00 L2 4
112784097 1221000100 2013-10-14 14:21:00 L4 1
.
.
.
取NewIndex = 1 即可得到要求的结果集
FID Barcode OnTime LineCode NewIndex
112784446332100010e 2013-10-14 13:25:00 L2 1
1127840971221000100 2013-10-14 14:21:00 L4 1
..
.