麻烦各位大侠
色号 批次 制单日起 匹号 品号
2-17 1024 2011-10-06 00:00:00.000 B0168 29268
2-17 1024 2011-10-07 00:00:00.000 B0172 29268
2-17 1024 2011-10-07 00:00:00.000 B0171 29268
2-17 1024 2011-10-07 00:00:00.000 B0170 29268
2-17 1024 2011-10-08 00:00:00.000 B0173 29268
2-17 1024 2011-10-09 00:00:00.000 B0175 29268
2-17 1024 2011-10-09 00:00:00.000 B0174 29268
2-17 1024 2011-10-11 00:00:00.000 B0176 29268
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-8 1025 2011-10-04 00:00:00.000 B0184 29268
2-8 1025 2011-10-04 00:00:00.000 B0183 29268
2-8 1025 2011-10-05 00:00:00.000 B0186 29268
2-8 1025 2011-10-05 00:00:00.000 B0185 29268
2-8 1025 2011-10-06 00:00:00.000 B0188 29268
2-8 1025 2011-10-06 00:00:00.000 B0187 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
2-8 1025 2011-10-07 00:00:00.000 B0190 29268
2-8 1025 2011-10-07 00:00:00.000 B0189 29268
怎么把同一个品号 色号 批次的最后三个日期和匹号取出来 如上表数据红色的部分，这些数据只是列了一点 表里面有很多的数据 想找每一个品号 色号 批次 下面最后三条数据 也就是日期每个品号 色号 批次 下面最后三条主句的日期和匹号 请大家帮忙 谢谢
------解决方案--------------------
- SQL code
if object_id('[TB]') is not null drop table [TB]gocreate 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 allselect '2-17',1024,'2011-10-07 00:00:00.000','B0172',29268 union allselect '2-17',1024,'2011-10-07 00:00:00.000','B0171',29268 union allselect '2-17',1024,'2011-10-07 00:00:00.000','B0170',29268 union allselect '2-17',1024,'2011-10-08 00:00:00.000','B0173',29268 union allselect '2-17',1024,'2011-10-09 00:00:00.000','B0175',29268 union allselect '2-17',1024,'2011-10-09 00:00:00.000','B0174',29268 union allselect '2-17',1024,'2011-10-11 00:00:00.000','B0176',29268 union allselect '2-17',1024,'2011-10-12 00:00:00.000','B0178',29268 union allselect '2-17',1024,'2011-10-12 00:00:00.000','B0177',29268 union allselect '2-8',1025,'2011-10-04 00:00:00.000','B0184',29268 union allselect '2-8',1025,'2011-10-04 00:00:00.000','B0183',29268 union allselect '2-8',1025,'2011-10-05 00:00:00.000','B0186',29268 union allselect '2-8',1025,'2011-10-05 00:00:00.000','B0185',29268 union allselect '2-8',1025,'2011-10-06 00:00:00.000','B0188',29268 union allselect '2-8',1025,'2011-10-06 00:00:00.000','B0187',29268 union allselect '2-8',1025,'2011-10-07 00:00:00.000','B0194',29268 union allselect '2-8',1025,'2011-10-07 00:00:00.000','B0193',29268 union allselect '2-8',1025,'2011-10-07 00:00:00.000','B0191',29268 union allselect '2-8',1025,'2011-10-07 00:00:00.000','B0190',29268 union allselect '2-8',1025,'2011-10-07 00:00:00.000','B0189',29268select * from [TB]SELECT 色号 ,批次,制单日起,匹号 ,品号FROM (SELECT *,ROW_NUMBER() OVER(PARTITION BY 品号,色号,批次 ORDER BY 制单日起 DESC) AS noFROM dbo.TB)TWHERE no <4/*色号 批次 制单日起 匹号 品号2-17 1024 2011-10-12 00:00:00.000 B0178 292682-17 1024 2011-10-12 00:00:00.000 B0177 292682-17 1024 2011-10-11 00:00:00.000 B0176 292682-8 1025 2011-10-07 00:00:00.000 B0194 292682-8 1025 2011-10-07 00:00:00.000 B0193 292682-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 tb select '2-17','1024','2011-10-07 00:00:00.000','B0170','29268'insert into tb select '2-17','1024','2011-10-08 00:00:00.000','B0173','29268'insert into tb select '2-17','1024','2011-10-09 00:00:00.000','B0175','29268'insert into tb select '2-17','1024','2011-10-09 00:00:00.000','B0174','29268'insert into tb select '2-17','1024','2011-10-11 00:00:00.000','B0176','29268'insert into tb select '2-17','1024','2011-10-12 00:00:00.000','B0178','29268'insert into tb select '2-17','1024','2011-10-12 00:00:00.000','B0177','29268'insert into tb select '2-8','1025','2011-10-04 00:00:00.000','B0184','29268'insert into tb select '2-8','1025','2011-10-04 00:00:00.000','B0183','29268'insert into tb select '2-8','1025','2011-10-05 00:00:00.000','B0186','29268'insert into tb select '2-8','1025','2011-10-05 00:00:00.000','B0185','29268'insert into tb select '2-8','1025','2011-10-06 00:00:00.000','B0188','29268'insert into tb select '2-8','1025','2011-10-06 00:00:00.000','B0187','29268'insert into tb select '2-8','1025','2011-10-07 00:00:00.000','B0194','29268'insert into tb select '2-8','1025','2011-10-07 00:00:00.000','B0193','29268'insert into tb select '2-8','1025','2011-10-07 00:00:00.000','B0191','29268'insert into tb select '2-8','1025','2011-10-07 00:00:00.000','B0190','29268'insert into tb select '2-8','1025','2011-10-07 00:00:00.000','B0189','29268'goselect * from(select ROW_NUMBER() over(partition by 品号,色号,批次 order by 制单日期 desc)rn,* from TB)t where rn<4order by 品号,制单日期/*rn 色号 批次 制单日期 匹号 品号-------------------- ---------- ---------- ----------------------- ---------- ----------1 2-8 1025 2011-10-07 00:00:00.000 B0194 292682 2-8 1025 2011-10-07 00:00:00.000 B0193 292683 2-8 1025 2011-10-07 00:00:00.000 B0191 292683 2-17 1024 2011-10-11 00:00:00.000 B0176 292681 2-17 1024 2011-10-12 00:00:00.000 B0178 292682 2-17 1024 2011-10-12 00:00:00.000 B0177 29268(6 行受影响)*/godrop table tb