--看看是不是你要的 create table T(num varchar(10), flag int,[date] datetime, value1 varchar(10), value2 varchar(10), value3 varchar(10)) insert into T select '000001', 0, '20070908' ,'p11', 'p22' , 'p3' union all select'000001' , 1, '20070910', 'p1' , 'p2' , 'p3' union all select'000001' , 2, '20070915' ,'p1' , 'p2' , 'p3' union all select'000001' , 3 , '20070918' ,'p11' , 'p2' , 'p31' union all select'000001' , 4 , '20070918' ,'p11' , 'p2' , 'p31' union all select'000001' , 5 , '20070920' ,'p1' , 'p2' , 'p3' union all select'000001' , 6 , '20070920', 'p1' , 'p2' , 'p3' union all select'000001' ,7 , '20070921' ,'p1' , 'p2' , 'p3' union all select'000001' ,8 , '20070922' ,'p1' , 'p2' , 'p3' union all select'000001' , 9 , '20070925' ,'p1' , 'p22', 'p3' union all select '000002', 0, '20070908' ,'p11', 'p22' , 'p3' union all select'000002' , 1, '20070910', 'p11' , 'p22' , 'p3' union all select'000002' , 2, '20070915' ,'p11' , 'p22' , 'p3' union all select'000002' , 3 , '20070918' ,'p11' , 'p2' , 'p31' union all select'000002' , 4 , '20070918' ,'p11' , 'p2' , 'p31' union all select'000002' , 5 , '20070920' ,'p1' , 'p2' , 'p3' union all select'000002' , 6 , '20070920', 'p1' , 'p2' , 'p3' union all select'000002' ,7 , '20070921' ,'p1' , 'p2' , 'p3' union all select'000002' ,8 , '20070922' ,'p1' , 'p2' , 'p3' union all select'000002' , 9 , '20070925' ,'p1' , 'p22', 'p3' GOselect * ,tmp=0 into #t from Tdeclare @tmp int,@value1 varchar(10),@value2 varchar(10),@value3 varchar(10) set @tmp=0 update #T set @tmp=case when @value1=value1 and @value2=value2 and @value3=value3 then @tmp else @tmp+1 end, @value1=value1, @value2=value2, @value3=value3, tmp=@tmpselect num,flag,date,value1,value2,value3 from #T where flag=0 union all select #T.num,#T.flag,#T.date,#T.value1,#T.value2,#T.value3 from #T inner join (select max(flag) as [flag],tmp from #T where tmp not in (select tmp from #T where flag=0) group by tmp) A on #T.tmp=A.tmp and #T.[flag]=A.[flag] where #T.flag<>0 order by num,flag/* num flag date value1 value2 value3 --------------------------------------------------------------------------- 000001 0 2007-09-08 00:00:00.000 p11 p22 p3 000001 2 2007-09-15 00:00:00.000 p1 p2 p3 000001 4 2007-09-18 00:00:00.000 p11 p2 p31 000001 8 2007-09-22 00:00:00.000 p1 p2 p3 000001 9 2007-09-25 00:00:00.000 p1 p22 p3 000002 0 2007-09-08 00:00:00.000 p11 p22 p3 000002 4 2007-09-18 00:00:00.000 p11 p2 p31 000002 8 2007-09-22 00:00:00.000 p1 p2 p3 000002 9 2007-09-25 00:00:00.000 p1 p22 p3*/drop table T,#T
--原始数据:@A declare @A table(物料编码 varchar(6),更新记录号 int,更新时间 datetime,属性1 varchar(3),属性2 varchar(3),属性3 varchar(3)) insert @A select '000001',0,'20070908','p11','p22','p3' union all select '000001',1,'20070910','p1','p2','p3' union all select '000001',2,'20070915','p1','p2','p3' union all select '000001',3,'20070918','p11','p2','p31' union all select '000001',4,'20070918','p11','p2','p31' union all select '000001',5,'20070920','p1','p2','p3' union all select '000001',6,'20070920','p1','p2','p3' union all select '000001',7,'20070921','p1','p2','p3' union all select '000001',8,'20070922','p1','p2','p3' union all select '000001',9,'20070925','p1','p22','p3'declare @Temp table(物料编码 varchar(6),更新记录号 int,更新时间 datetime,P1 varchar(3),P2 varchar(3),P3 varchar(3),P4 int) insert @Temp select *,null from @A order by 更新记录号declare @P1 varchar(3),@P2 varchar(3),@P3 varchar(3),@P4 int set @P4=1 update @Temp set @P4=case when @P1<>P1 or @P2<>P2 or @P3<>P3 then @P4+1 else @P4 end,@P1=P1,@P2=P2,@P3=P3,P4=@P4 select a.* from @A a join (select 更新记录号=max(更新记录号) from @Temp group by P4) b on a.更新记录号=b.更新记录号/* 物料编码 更新记录号 更新时间 属性1 属性2 属性3 000001 0 2007-09-08 00:00:00.000 p11 p22 p3 000001 2 2007-09-15 00:00:00.000 p1 p2 p3 000001 4 2007-09-18 00:00:00.000 p11 p2 p31 000001 8 2007-09-22 00:00:00.000 p1 p2 p3 000001 9 2007-09-25 00:00:00.000 p1 p22 p3 */
--原始数据:@A declare @A table(物料编码 varchar(6),更新记录号 int,更新时间 datetime,属性1 varchar(3),属性2 varchar(3),属性3 varchar(3)) insert @A select '000001',0,'20070908','p11','p22','p3' union all select '000001',1,'20070910','p1','p2','p3' union all select '000001',2,'20070915','p1','p2','p3' union all select '000001',3,'20070918','p11','p2','p31' union all select '000001',4,'20070918','p11','p2','p31' union all select '000001',5,'20070920','p1','p2','p3' union all select '000001',6,'20070920','p1','p2','p3' union all select '000001',7,'20070921','p1','p2','p3' union all select '000001',8,'20070922','p1','p2','p3' union all select '000001',9,'20070925','p1','p22','p3'declare @Temp table(物料编码 varchar(6),更新记录号 int,更新时间 datetime,P1 varchar(3),P2 varchar(3),P3 varchar(3),P4 int) insert @Temp select *,null from @A order by 更新记录号declare @P1 varchar(3),@P2 varchar(3),@P3 varchar(3),@P4 int set @P4=1 update @Temp set @P4=case when @P1<>P1 or @P2<>P2 or @P3<>P3 then @P4+1 else @P4 end,@P1=P1,@P2=P2,@P3=P3,P4=@P4 -- 0是一定要的: select a.* from @A a join (select 更新记录号=0 union select max(更新记录号) from @Temp group by P4) b on a.更新记录号=b.更新记录号/* 物料编码 更新记录号 更新时间 属性1 属性2 属性3 000001 0 2007-09-08 00:00:00.000 p11 p22 p3 000001 2 2007-09-15 00:00:00.000 p1 p2 p3 000001 4 2007-09-18 00:00:00.000 p11 p2 p31 000001 8 2007-09-22 00:00:00.000 p1 p2 p3 000001 9 2007-09-25 00:00:00.000 p1 p22 p3 */
--把1的属性數據,改成和0一樣,1也會查出來的 select '000001',1,'20070910','p11','p22','p3' union all
------------ select '000001',0,'20070908','p11','p22','p3' union all select '000001',1,'20070910','p11','p22','p3' union all select '000001',2,'20070915','p1','p2','p3' union all ... ------------ 0是楼主要求保留的啊,我第一个回复只有1没有0的。如果跟0的相同,只要0其它不要,那很简单:select 更新记录号=0 union select max(更新记录号) from @Temp where P4>1 group by P4这样就行OK啦。
create table T(num varchar(10), flag int,[date] datetime, value1 varchar(10), value2 varchar(10), value3 varchar(10))
insert into T
select '000001', 0, '20070908' ,'p11', 'p22' , 'p3' union all
select'000001' , 1, '20070910', 'p1' , 'p2' , 'p3' union all
select'000001' , 2, '20070915' ,'p1' , 'p2' , 'p3' union all
select'000001' , 3 , '20070918' ,'p11' , 'p2' , 'p31' union all
select'000001' , 4 , '20070918' ,'p11' , 'p2' , 'p31' union all
select'000001' , 5 , '20070920' ,'p1' , 'p2' , 'p3' union all
select'000001' , 6 , '20070920', 'p1' , 'p2' , 'p3' union all
select'000001' ,7 , '20070921' ,'p1' , 'p2' , 'p3' union all
select'000001' ,8 , '20070922' ,'p1' , 'p2' , 'p3' union all
select'000001' , 9 , '20070925' ,'p1' , 'p22', 'p3' union all
select '000002', 0, '20070908' ,'p11', 'p22' , 'p3' union all
select'000002' , 1, '20070910', 'p11' , 'p22' , 'p3' union all
select'000002' , 2, '20070915' ,'p11' , 'p22' , 'p3' union all
select'000002' , 3 , '20070918' ,'p11' , 'p2' , 'p31' union all
select'000002' , 4 , '20070918' ,'p11' , 'p2' , 'p31' union all
select'000002' , 5 , '20070920' ,'p1' , 'p2' , 'p3' union all
select'000002' , 6 , '20070920', 'p1' , 'p2' , 'p3' union all
select'000002' ,7 , '20070921' ,'p1' , 'p2' , 'p3' union all
select'000002' ,8 , '20070922' ,'p1' , 'p2' , 'p3' union all
select'000002' , 9 , '20070925' ,'p1' , 'p22', 'p3' GOselect * ,tmp=0 into #t from Tdeclare @tmp int,@value1 varchar(10),@value2 varchar(10),@value3 varchar(10)
set @tmp=0
update #T
set @tmp=case when @value1=value1 and @value2=value2 and @value3=value3
then @tmp
else @tmp+1 end,
@value1=value1,
@value2=value2,
@value3=value3,
tmp=@tmpselect num,flag,date,value1,value2,value3 from #T where flag=0
union all
select #T.num,#T.flag,#T.date,#T.value1,#T.value2,#T.value3 from #T
inner join
(select max(flag) as [flag],tmp
from #T
where tmp not in (select tmp from #T where flag=0)
group by tmp) A
on #T.tmp=A.tmp and #T.[flag]=A.[flag]
where #T.flag<>0
order by num,flag/*
num flag date value1 value2 value3
---------------------------------------------------------------------------
000001 0 2007-09-08 00:00:00.000 p11 p22 p3
000001 2 2007-09-15 00:00:00.000 p1 p2 p3
000001 4 2007-09-18 00:00:00.000 p11 p2 p31
000001 8 2007-09-22 00:00:00.000 p1 p2 p3
000001 9 2007-09-25 00:00:00.000 p1 p22 p3
000002 0 2007-09-08 00:00:00.000 p11 p22 p3
000002 4 2007-09-18 00:00:00.000 p11 p2 p31
000002 8 2007-09-22 00:00:00.000 p1 p2 p3
000002 9 2007-09-25 00:00:00.000 p1 p22 p3*/drop table T,#T
declare @A table(物料编码 varchar(6),更新记录号 int,更新时间 datetime,属性1 varchar(3),属性2 varchar(3),属性3 varchar(3))
insert @A
select '000001',0,'20070908','p11','p22','p3' union all
select '000001',1,'20070910','p1','p2','p3' union all
select '000001',2,'20070915','p1','p2','p3' union all
select '000001',3,'20070918','p11','p2','p31' union all
select '000001',4,'20070918','p11','p2','p31' union all
select '000001',5,'20070920','p1','p2','p3' union all
select '000001',6,'20070920','p1','p2','p3' union all
select '000001',7,'20070921','p1','p2','p3' union all
select '000001',8,'20070922','p1','p2','p3' union all
select '000001',9,'20070925','p1','p22','p3'declare @Temp table(物料编码 varchar(6),更新记录号 int,更新时间 datetime,P1 varchar(3),P2 varchar(3),P3 varchar(3),P4 int)
insert @Temp select *,null from @A order by 更新记录号declare @P1 varchar(3),@P2 varchar(3),@P3 varchar(3),@P4 int
set @P4=1
update @Temp set @P4=case when @P1<>P1 or @P2<>P2 or @P3<>P3 then @P4+1 else @P4 end,@P1=P1,@P2=P2,@P3=P3,P4=@P4
select a.* from @A a join (select 更新记录号=max(更新记录号) from @Temp group by P4) b on a.更新记录号=b.更新记录号/*
物料编码 更新记录号 更新时间 属性1 属性2 属性3
000001 0 2007-09-08 00:00:00.000 p11 p22 p3
000001 2 2007-09-15 00:00:00.000 p1 p2 p3
000001 4 2007-09-18 00:00:00.000 p11 p2 p31
000001 8 2007-09-22 00:00:00.000 p1 p2 p3
000001 9 2007-09-25 00:00:00.000 p1 p22 p3
*/
declare @A table(物料编码 varchar(6),更新记录号 int,更新时间 datetime,属性1 varchar(3),属性2 varchar(3),属性3 varchar(3))
insert @A
select '000001',0,'20070908','p11','p22','p3' union all
select '000001',1,'20070910','p1','p2','p3' union all
select '000001',2,'20070915','p1','p2','p3' union all
select '000001',3,'20070918','p11','p2','p31' union all
select '000001',4,'20070918','p11','p2','p31' union all
select '000001',5,'20070920','p1','p2','p3' union all
select '000001',6,'20070920','p1','p2','p3' union all
select '000001',7,'20070921','p1','p2','p3' union all
select '000001',8,'20070922','p1','p2','p3' union all
select '000001',9,'20070925','p1','p22','p3'declare @Temp table(物料编码 varchar(6),更新记录号 int,更新时间 datetime,P1 varchar(3),P2 varchar(3),P3 varchar(3),P4 int)
insert @Temp select *,null from @A order by 更新记录号declare @P1 varchar(3),@P2 varchar(3),@P3 varchar(3),@P4 int
set @P4=1
update @Temp set @P4=case when @P1<>P1 or @P2<>P2 or @P3<>P3 then @P4+1 else @P4 end,@P1=P1,@P2=P2,@P3=P3,P4=@P4
-- 0是一定要的:
select a.* from @A a join (select 更新记录号=0 union select max(更新记录号) from @Temp group by P4) b on a.更新记录号=b.更新记录号/*
物料编码 更新记录号 更新时间 属性1 属性2 属性3
000001 0 2007-09-08 00:00:00.000 p11 p22 p3
000001 2 2007-09-15 00:00:00.000 p1 p2 p3
000001 4 2007-09-18 00:00:00.000 p11 p2 p31
000001 8 2007-09-22 00:00:00.000 p1 p2 p3
000001 9 2007-09-25 00:00:00.000 p1 p22 p3
*/
select '000001',1,'20070910','p11','p22','p3' union all
select '000001',0,'20070908','p11','p22','p3' union all
select '000001',1,'20070910','p11','p22','p3' union all
select '000001',2,'20070915','p1','p2','p3' union all
...
------------
0是楼主要求保留的啊,我第一个回复只有1没有0的。如果跟0的相同,只要0其它不要,那很简单:select 更新记录号=0 union select max(更新记录号) from @Temp where P4>1 group by P4这样就行OK啦。