sql 行列转换
麻烦各位大侠
色号 批次 制单日起 匹号 品号
2-1710242011-10-06 00:00:00.000B0168 29268
2-1710242011-10-07 00:00:00.000B0172 29268
2-1710242011-10-07 00:00:00.000B0171 29268
2-1710242011-10-07 00:00:00.000B0170 29268
2-1710242011-10-08 00:00:00.000B0173 29268
2-1710242011-10-09 00:00:00.000B0175 29268
2-1710242011-10-09 00:00:00.000B0174 29268
2-1710242011-10-11 00:00:00.000B0176 29268
2-1710242011-10-12 00:00:00.000B0178 29268
2-1710242011-10-12 00:00:00.000B0177 29268
2-810252011-10-04 00:00:00.000B0184 29268
2-810252011-10-04 00:00:00.000B0183 29268
2-810252011-10-05 00:00:00.000B0186 29268
2-810252011-10-05 00:00:00.000B0185 29268
2-810252011-10-06 00:00:00.000B0188 29268
2-810252011-10-06 00:00:00.000B0187 29268
2-810252011-10-07 00:00:00.000B0194 29268
2-810252011-10-07 00:00:00.000B0193 29268
2-810252011-10-07 00:00:00.000B0191 29268
2-810252011-10-07 00:00:00.000B0190 29268
2-810252011-10-07 00:00:00.000B0189 29268
怎么把同一个品号 色号 批次的最后三个日期和匹号取出来 如上表数据红色的部分，这些数据只是列了一点 表里面有很多的数据 想找每一个品号 色号 批次 下面最后三条数据 也就是日期每个品号 色号 批次 下面最后三条主句的日期和匹号 请大家帮忙 谢谢
[解决办法]
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*/
[解决办法]
同一日期的匹号如何区分?
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
[解决办法]
use Tempdbgo--> --> if not object_id(N'Tempdb..#T') is null drop table #TGoCreate table #T([色号] nvarchar(4),[批次] int,[制单日起] Datetime,[匹号] nvarchar(5),[品号] int)Insert #Tselect N'2-17',1024,'2011-10-06',N'B0168',29268 union allselect N'2-17',1024,'2011-10-07',N'B0172',29268 union allselect N'2-17',1024,'2011-10-07',N'B0171',29268 union allselect N'2-17',1024,'2011-10-07',N'B0170',29268 union allselect N'2-17',1024,'2011-10-08',N'B0173',29268 union allselect N'2-17',1024,'2011-10-09',N'B0175',29268 union allselect N'2-17',1024,'2011-10-09',N'B0174',29268 union allselect N'2-17',1024,'2011-10-11',N'B0176',29268 union allselect N'2-17',1024,'2011-10-12',N'B0178',29268 union allselect N'2-17',1024,'2011-10-12',N'B0177',29268 union allselect N'2-8',1025,'2011-10-04',N'B0184',29268 union allselect N'2-8',1025,'2011-10-04',N'B0183',29268 union allselect N'2-8',1025,'2011-10-05',N'B0186',29268 union allselect N'2-8',1025,'2011-10-05',N'B0185',29268 union allselect N'2-8',1025,'2011-10-06',N'B0188',29268 union allselect N'2-8',1025,'2011-10-06',N'B0187',29268 union allselect N'2-8',1025,'2011-10-07',N'B0194',29268 union allselect N'2-8',1025,'2011-10-07',N'B0193',29268 union allselect N'2-8',1025,'2011-10-07',N'B0191',29268 union allselect N'2-8',1025,'2011-10-07',N'B0190',29268 union allselect N'2-8',1025,'2011-10-07',N'B0189',29268GoSELECT t2.*FROM (Select DISTINCT [色号],[批次],品号 from #T)tCROSS APPLY(SELECT TOP 3 * FROM #T WHERE [色号]=t.[色号] AND [批次]=t.[批次] AND 品号=t.品号 ORDER BY [制单日起] desc)t2ORDER BY 1,2,4/*色号 批次 制单日起 匹号 品号2-17 1024 2011-10-11 00:00:00.000 B0176 292682-17 1024 2011-10-12 00:00:00.000 B0177 292682-17 1024 2011-10-12 00:00:00.000 B0178 292682-8 1025 2011-10-07 00:00:00.000 B0191 292682-8 1025 2011-10-07 00:00:00.000 B0193 292682-8 1025 2011-10-07 00:00:00.000 B0194 29268*/
[解决办法]
use Tempdbgo--> --> if not object_id(N'Tempdb..#T') is null drop table #TGoCreate table #T([色号] nvarchar(4),[批次] int,[制单日起] Datetime,[匹号] nvarchar(5),[品号] int)Insert #Tselect N'2-17',1024,'2011-10-06',N'B0168',29268 union allselect N'2-17',1024,'2011-10-07',N'B0172',29268 union allselect N'2-17',1024,'2011-10-07',N'B0171',29268 union allselect N'2-17',1024,'2011-10-07',N'B0170',29268 union allselect N'2-17',1024,'2011-10-08',N'B0173',29268 union allselect N'2-17',1024,'2011-10-09',N'B0175',29268 union allselect N'2-17',1024,'2011-10-09',N'B0174',29268 union allselect N'2-17',1024,'2011-10-11',N'B0176',29268 union allselect N'2-17',1024,'2011-10-12',N'B0178',29268 union allselect N'2-17',1024,'2011-10-12',N'B0177',29268 union allselect N'2-8',1025,'2011-10-04',N'B0184',29268 union allselect N'2-8',1025,'2011-10-04',N'B0183',29268 union allselect N'2-8',1025,'2011-10-05',N'B0186',29268 union allselect N'2-8',1025,'2011-10-05',N'B0185',29268 union allselect N'2-8',1025,'2011-10-06',N'B0188',29268 union allselect N'2-8',1025,'2011-10-06',N'B0187',29268 union allselect N'2-8',1025,'2011-10-07',N'B0194',29268 union allselect N'2-8',1025,'2011-10-07',N'B0193',29268 union allselect N'2-8',1025,'2011-10-07',N'B0191',29268 union allselect N'2-8',1025,'2011-10-07',N'B0190',29268 union allselect N'2-8',1025,'2011-10-07',N'B0189',29268GoSELECT *,row=IDENTITY(INT,1,1)INTO # FROM #T AS tSELECT 色号, 批次, 制单日起, 匹号, 品号FROM # AS tWHERE row in(SELECT TOP 3 row FROM # WHERE [色号]=t.[色号] AND [批次]=t.[批次] AND 品号=t.品号 ORDER BY row desc)/*色号 批次 制单日起 匹号 品号2-17 1024 2011-10-11 00:00:00.000 B0176 292682-17 1024 2011-10-12 00:00:00.000 B0178 292682-17 1024 2011-10-12 00:00:00.000 B0177 292682-8 1025 2011-10-07 00:00:00.000 B0191 292682-8 1025 2011-10-07 00:00:00.000 B0190 292682-8 1025 2011-10-07 00:00:00.000 B0189 29268*/