declare @t table
(aaa varchar(20),bbb varchar(20),ccc int,ddd int)insert @t
select '001','B001',102,100 union all
select '001','B002',0,90 union all
select '001','B003',0,89 union all
select '001','B004',0,87 union all
select '001','B005',0,87 union all
select '001','B006',0,85 union all
select '001','B007',0,83 union all
select '002','B001',234,230 union all
select '002','B003',0,210 union all
select '002','B005',0,208 union all
select '002','B006',0,206 union all
select '002','B007',0,199 union all
select '002','B008',0,199 union all
select '002','B009',0,180 union all
select '003','B001',140,130 union all
select '003','B004',0,109 union all
select '003','B005',0,108 union all
select '003','B008',0,103 union all
select '003','B009',0,98 union all
select '003','B010',0,98 union all
select '003','B017',0,98update @t set ccc=b.ccc
from @t a,
(
select aaa,bbb,ccc=
isnull((select min(ddd) from @t b where a.aaa=b.aaa and a.bbb>b.bbb),a.ccc),ddd from @t a
) b where a.aaa=b.aaa and a.bbb=b.bbb
select * from @taaa bbb ccc ddd
-------------------- -------------------- ----------- -----------
001 B001 102 100
001 B002 100 90
001 B003 90 89
001 B004 89 87
001 B005 87 87
001 B006 87 85
001 B007 85 83
002 B001 234 230
002 B003 230 210
002 B005 210 208
002 B006 208 206
002 B007 206 199
002 B008 199 199
002 B009 199 180
003 B001 140 130
003 B004 130 109
003 B005 109 108
003 B008 108 103
003 B009 103 98
003 B010 98 98
003 B017 98 98(所影响的行数为 21 行)
(aaa varchar(20),bbb varchar(20),ccc int,ddd int)insert @t
select '001','B001',102,100 union all
select '001','B002',0,90 union all
select '001','B003',0,89 union all
select '001','B004',0,87 union all
select '001','B005',0,87 union all
select '001','B006',0,85 union all
select '001','B007',0,83 union all
select '002','B001',234,230 union all
select '002','B003',0,210 union all
select '002','B005',0,208 union all
select '002','B006',0,206 union all
select '002','B007',0,199 union all
select '002','B008',0,199 union all
select '002','B009',0,180 union all
select '003','B001',140,130 union all
select '003','B004',0,109 union all
select '003','B005',0,108 union all
select '003','B008',0,103 union all
select '003','B009',0,98 union all
select '003','B010',0,98 union all
select '003','B017',0,98update @t set ccc=b.ccc
from @t a,
(
select aaa,bbb,ccc=
isnull((select min(ddd) from @t b where a.aaa=b.aaa and a.bbb>b.bbb),a.ccc),ddd from @t a
) b where a.aaa=b.aaa and a.bbb=b.bbb
select * from @taaa bbb ccc ddd
-------------------- -------------------- ----------- -----------
001 B001 102 100
001 B002 100 90
001 B003 90 89
001 B004 89 87
001 B005 87 87
001 B006 87 85
001 B007 85 83
002 B001 234 230
002 B003 230 210
002 B005 210 208
002 B006 208 206
002 B007 206 199
002 B008 199 199
002 B009 199 180
003 B001 140 130
003 B004 130 109
003 B005 109 108
003 B008 108 103
003 B009 103 98
003 B010 98 98
003 B017 98 98(所影响的行数为 21 行)
所以用min(ddd)找出的结果不一定对。
例如:declare @t table
(aaa varchar(20),bbb varchar(20),ccc int,ddd int)insert @t
select '001','B001',102,100 union all
select '001','B002',0,90 union all
select '001','B003',0,99 union all
select '001','B004',0,87 union all
select '001','B005',0,87 union all
select '001','B006',0,85 union all
select '001','B007',0,83 union all
select '002','B001',234,230 union all
select '002','B003',0,210 union all
select '002','B005',0,208 union all
select '002','B006',0,206 union all
select '002','B007',0,199 union all
select '002','B008',0,199 union all
select '002','B009',0,180select aaa,bbb,ccc=
isnull((select min(ddd) from @t b where a.aaa=b.aaa and a.bbb>b.bbb),a.ccc),ddd from @t a
aaa bbb ccc ddd
-------------------- -------------------- ----------- -----------
001 B001 102 100
001 B002 100 90
001 B003 90 99001 B004 90 87 (这一条错了,正确值应该是:99 87)001 B005 87 87
001 B006 87 85
001 B007 85 83
002 B001 234 230
002 B003 230 210
002 B005 210 208
002 B006 208 206
002 B007 206 199
002 B008 199 199
002 B009 199 180(所影响的行数为 14 行)
(aaa varchar(20),bbb varchar(20),ccc int,ddd int)insert @t
select '001','B001',102,100 union all
select '001','B002',0,90 union all
select '001','B003',0,89 union all
select '001','B004',0,87 union all
select '001','B005',0,87 union all
select '001','B006',0,85 union all
select '001','B007',0,83 union all
select '002','B001',234,230 union all
select '002','B003',0,210 union all
select '002','B005',0,208 union all
select '002','B006',0,206 union all
select '002','B007',0,199 union all
select '002','B008',0,199 union all
select '002','B009',0,180 union all
select '003','B001',140,130 union all
select '003','B004',0,109 union all
select '003','B005',0,108 union all
select '003','B008',0,103 union all
select '003','B009',0,98 union all
select '003','B010',0,98 union all
select '003','B017',0,98update @t set ccc=case when ccc>0 then ccc else (select top 1 ddd from @t where aaa=T.aaa and bbb<T.bbb order by bbb DESC) end
from
@t T
select * from @t
from
@t T再次感谢
scmail81(琳·风の狼)
samfeng_2003(凤翼天翔)
from
@t T再次感谢
scmail81(琳·风の狼)
samfeng_2003(凤翼天翔)