表的结构如下:
PR为项目代号,Item为小分类,Date为出厂日期。
要求:如PR=P001,Item 为1000的日期为Item为1001/1002/1...的日期的最大值,如本例为2005-1-10
PR=P001,Item 为2000的日期为Item为2001/2002/2...的日期的最大值,如本例为2006-1-20
要求更新ITEM为%000的日期
注意:如PR=P0001,ITEM=3000的日期,但是没有Item为3001/。。的则在PR=P001,item=3000的date填入错误信息PR Item Date
P001 1000
P001 1001 2005-1-2
P001 1002 2005-1-10
P001 1050
P001 2000
P001 2001 2006-1-20
P001 3000
P002 1000
P002 1001 2007-1-1
PR为项目代号,Item为小分类,Date为出厂日期。
要求:如PR=P001,Item 为1000的日期为Item为1001/1002/1...的日期的最大值,如本例为2005-1-10
PR=P001,Item 为2000的日期为Item为2001/2002/2...的日期的最大值,如本例为2006-1-20
要求更新ITEM为%000的日期
注意:如PR=P0001,ITEM=3000的日期,但是没有Item为3001/。。的则在PR=P001,item=3000的date填入错误信息PR Item Date
P001 1000
P001 1001 2005-1-2
P001 1002 2005-1-10
P001 1050
P001 2000
P001 2001 2006-1-20
P001 3000
P002 1000
P002 1001 2007-1-1
P001 1000
P001 1001 2005-1-2
P001 1002 2005-1-10
P001 1050
P001 2000
P001 2001 2006-1-20
P001 3000
P002 1000
P002 1001 2007-1-1
SELECT PR,Item
,ISNULL((SELECT MAX(Date) FROM [表2] GROUP BY Item HAVING [表2].Item = [表1].Item),'') AS Date
FROM [表1]
,ISNULL((SELECT MAX(Date) FROM [表2] GROUP BY PR,Item HAVING [表2].PR = [表1].PR AND [表2].Item = [表1].Item),'') AS Date
FROM [表1]
Set Nocount On
declare @2 table([PR] nvarchar(4),[Item] int,[Date] Datetime)
Insert @2
select N'P001',1000,null union all
select N'P001',1001,'2005-1-2' union all
select N'P001',1002,'2005-1-10' union all
select N'P001',1050,null union all
select N'P001',2000,null union all
select N'P001',2001,'2006-1-20' union all
select N'P001',3000,null union all
select N'P002',1000,null union all
select N'P002',1001,'2007-1-1'
Update A
Set [Date] =(Select Top 1 [Date] From @2 Where [PR]=A.[PR] And Left(Item,1)=Left(A.Item,1) And Item<>A.Item Order By [Date] Desc)
From @2 A
Where Item Like '_000' Select * from @2 /*
PR Item Date
---- ----------- -----------------------
P001 1000 2005-01-10 00:00:00.000
P001 1001 2005-01-02 00:00:00.000
P001 1002 2005-01-10 00:00:00.000
P001 1050 NULL
P001 2000 2006-01-20 00:00:00.000
P001 2001 2006-01-20 00:00:00.000
P001 3000 NULL
P002 1000 2007-01-01 00:00:00.000
P002 1001 2007-01-01 00:00:00.000
*/