我觉得楼上的办法是指整体,没有分段排序。 =======================================说说咱的不成熟的看法(别笑咱啊,呵~~~);看题目是:CW进行分组,按照Depth1进行排序,所以只用这两个字段select cw, depth1,null ids into #b from table1 order by cw,depth1 asc update #b set ids=1 from (select cw,min(depth1) depth1 from #b group by cw) m where #b.cw=m.cw and #b.depth1=m.depth1SELECT cw,count(*) cs into #cs from gd group by cw-- 循环次数 declare @a int ,@b int set @a=2 select @b=max(cs) from #cs while @a<=@b begin update #b set ids=@a from (select cw,min(depth1) depth1 from #b where ids is null group by cw) m where #b.cw=m.cw and #b.depth1=m.depth1 set @a=@a+1 end 结果:select * from #b
呵呵,不好意思啊,下面的gd表就是table1表,忘记改了 SELECT cw,count(*) cs into #cs from gd group by cw-- 循环次数
declare @temptable table ( CW varchar(2) , Depth1 int, Depth2 int ) insert @temptable select 'A',2500,2512 union select 'B',2511,2513 union select 'A',2500,2512 union select 'A',2711,2080 union select 'A',2900,2901 union select 'B',1153,1787select * from @temptableselect cw,Depth1,Depth2,ID = (select count(*) from @temptable b where a.depth1 >= b.depth1 and a.cw = b.cw group by cw) from @temptable a order by cw,Depth1
=======================================说说咱的不成熟的看法(别笑咱啊,呵~~~);看题目是:CW进行分组,按照Depth1进行排序,所以只用这两个字段select cw, depth1,null ids into #b from table1
order by cw,depth1 asc
update #b set ids=1 from (select cw,min(depth1) depth1 from #b group by cw) m
where #b.cw=m.cw and #b.depth1=m.depth1SELECT cw,count(*) cs into #cs from gd group by cw-- 循环次数
declare @a int ,@b int set @a=2
select @b=max(cs) from #cs
while @a<=@b
begin
update #b set ids=@a from (select cw,min(depth1) depth1 from #b where ids is null group by cw) m
where #b.cw=m.cw and #b.depth1=m.depth1
set @a=@a+1
end
结果:select * from #b
呵呵,不好意思啊,下面的gd表就是table1表,忘记改了
SELECT cw,count(*) cs into #cs from gd group by cw-- 循环次数
declare @temptable table (
CW varchar(2) ,
Depth1 int,
Depth2 int
)
insert @temptable select 'A',2500,2512
union select 'B',2511,2513
union select 'A',2500,2512
union select 'A',2711,2080
union select 'A',2900,2901
union select 'B',1153,1787select * from @temptableselect cw,Depth1,Depth2,ID = (select count(*) from @temptable b where a.depth1 >= b.depth1 and a.cw = b.cw group by cw) from @temptable a order by cw,Depth1