if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (色号 nvarchar(8),批次 int,制单日起 datetime,匹号 nvarchar(10),品号 int)
insert into [TB]
select '2-17',1024,'2011-10-06 00:00:00.000','B0168',29268 union all
select '2-17',1024,'2011-10-07 00:00:00.000','B0172',29268 union all
select '2-17',1024,'2011-10-07 00:00:00.000','B0171',29268 union all
select '2-17',1024,'2011-10-07 00:00:00.000','B0170',29268 union all
select '2-17',1024,'2011-10-08 00:00:00.000','B0173',29268 union all
select '2-17',1024,'2011-10-09 00:00:00.000','B0175',29268 union all
select '2-17',1024,'2011-10-09 00:00:00.000','B0174',29268 union all
select '2-17',1024,'2011-10-11 00:00:00.000','B0176',29268 union all
select '2-17',1024,'2011-10-12 00:00:00.000','B0178',29268 union all
select '2-17',1024,'2011-10-12 00:00:00.000','B0177',29268 union all
select '2-8',1025,'2011-10-04 00:00:00.000','B0184',29268 union all
select '2-8',1025,'2011-10-04 00:00:00.000','B0183',29268 union all
select '2-8',1025,'2011-10-05 00:00:00.000','B0186',29268 union all
select '2-8',1025,'2011-10-05 00:00:00.000','B0185',29268 union all
select '2-8',1025,'2011-10-06 00:00:00.000','B0188',29268 union all
select '2-8',1025,'2011-10-06 00:00:00.000','B0187',29268 union all
select '2-8',1025,'2011-10-07 00:00:00.000','B0194',29268 union all
select '2-8',1025,'2011-10-07 00:00:00.000','B0193',29268 union all
select '2-8',1025,'2011-10-07 00:00:00.000','B0191',29268 union all
select '2-8',1025,'2011-10-07 00:00:00.000','B0190',29268 union all
select '2-8',1025,'2011-10-07 00:00:00.000','B0189',29268

select * from [TB]

SELECT 色号 ,批次,制单日起,匹号 ,品号
FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY 品号,色号,批次 ORDER BY 制单日起 DESC) AS no
FROM dbo.TB)T
WHERE no <4

/*
色号    批次    制单日起    匹号    品号
2-17    1024    2011-10-12 00:00:00.000    B0178    29268
2-17    1024    2011-10-12 00:00:00.000    B0177    29268
2-17    1024    2011-10-11 00:00:00.000    B0176    29268
2-8    1025    2011-10-07 00:00:00.000    B0194    29268
2-8    1025    2011-10-07 00:00:00.000    B0193    29268
2-8    1025    2011-10-07 00:00:00.000    B0191    29268
*/

------解决方案--------------------
同一日期的匹号如何区分?
SQL code
create table TB(色号 varchar(10),批次 varchar(10),制单日期 datetime,匹号 varchar(10),品号 varchar(10))
insert into tb select '2-17','1024','2011-10-06 00:00:00.000','B0168','29268'
insert into tb select '2-17','1024','2011-10-07 00:00:00.000','B0172','29268'
insert into tb select '2-17','1024','2011-10-07 00:00:00.000','B0171','29268'
insert into