分类排序
表格如下：
se id h
11 01 sa
11 02 pa
11 03 em
11 04 ch
11 05 ca
.. .. ..
12 01 sa
12 02 pa
12 03 em
.. .. ..
根据h升序，重新设置ID
希望得到如下结果：
se id h
11 01 ca
11 02 ch
11 03 em
11 04 pa
11 05 sa
12 01 em
12 02 pa
12 03 sa
[解决办法]
declare @t table(se int,id varchar(10),h varchar(10))
insert @t select 11, '01 ', 'sa '
insert @t select 11, '02 ', 'pa '
insert @t select 11, '03 ', 'em '
insert @t select 11, '04 ', 'ch '
insert @t select 11, '05 ', 'ca '
insert @t select 12, '01 ', 'sa '
insert @t select 12, '02 ', 'pa '
insert @t select 12, '03 ', 'em '
select se,id,
(select h from @t b
where b.se=a.se and (select count(1) from @t where se=b.se and h <=b.h)=a.id) h
from @t a
--结果
se id h
----------- ---------- ----------
11 01 ca
11 02 ch
11 03 em
11 04 pa
11 05 sa
12 01 em
12 02 pa
12 03 sa
（所影响的行数为 8 行）
[解决办法]
create table T(se int, id varchar(10), h varchar(10))
insert T select 11, '01 ', 'sa '
union all select 11, '02 ', 'pa '
union all select 11, '03 ', 'em '
union all select 11, '04 ', 'ch '
union all select 11, '05 ', 'ca '
union all select 12, '01 ', 'sa '
union all select 12, '02 ', 'pa '
union all select 12, '03 ', 'em '
select se,
id=(select count(*) from T where se=tmp.se and h <=tmp.h),
h
from T as tmp
order by se, h
--result
se id h
----------- ----------- ----------
11 1 ca
11 2 ch
11 3 em
11 4 pa
11 5 sa
12 1 em
12 2 pa
12 3 sa
(8 row(s) affected)
