关于自动编号方面的问题!!!
对于如下表:table5
col1
a
a
b
b
b
c
c
我要得到
no col1
1 a
2 a
1 b
2 b
3 b
1 c
2 c
在下也有一个苯的方法
declare @i int
declare @s varchar(50)
declare @s1 varchar(50)
declare @table table (no int,col1 varchar(50))
set @i=1
declare tempcursor cursor local for select * from table5 order by col1
open tempcursor
fetch next from tempcursor
into @s
insert into @table
values(@i,@s)
while @@fetch_status = 0
begin
fetch next from tempcursor
into @s1
if @s1=@s
begin
set @i=@i+1
set @s=@s1
end
else
begin
set @i=1
set @s=@s1
end
insert into @table
values(@i,@s)
end
close tempcursor
deallocate tempcursor
select * from @table
但是感觉很不爽,各位大侠可以指定好的方法吗?
[解决办法]
select id=identity(int,1,1),col1 into #t from table5 order by col1
select no=(select count(*) from #t b where b.col1=a.col1 and b.id <=a.id),col1
from #t a
[解决办法]
有一表
a b c
7 aa 153
9 aa 152
6 aa 120
8 aa 168
5 bb 159
7 bb 179
8 bb 149
9 bb 139
6 bb 169
对b列中的值来分类排序并分别加一序号,形成一新表
px a b c
1 6 aa 120
2 9 aa 152
3 7 aa 153
4 8 aa 168
1 9 bb 139
2 8 bb 149
3 5 bb 159
4 6 bb 169
5 7 bb 179
declare @tab table(a int,b varchar(2),c int)
insert @tab values(7, 'aa ',153)
insert @tab values(9, 'aa ',152)
insert @tab values(6, 'aa ',120)
insert @tab values(8, 'aa ',168)
insert @tab values(5, 'bb ',159)
insert @tab values(7, 'bb ',179)
insert @tab values(8, 'bb ',149)
insert @tab values(9, 'bb ',139)
insert @tab values(6, 'bb ',169)
select * from @tab
select px=(select count(1) from @tab where b=a.b and c <a.c)+1 , a,b,c from @tab a
order by b , c
px a b c
----------- ----------- ---- -----------
1 6 aa 120
2 9 aa 152
3 7 aa 153
4 8 aa 168
1 9 bb 139
2 8 bb 149
3 5 bb 159
4 6 bb 169
5 7 bb 179
（所影响的行数为 9 行）
在上面例中我们看到，以B分类排序，C是从小到大，如果C从大到小排序，即下面结果：
px a b c
1 8 aa 168
2 9 aa 153
3 7 aa 152
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139
declare @tab table(a int,b varchar(2),c int)
insert @tab values(7, 'aa ',153)
insert @tab values(9, 'aa ',152)
insert @tab values(6, 'aa ',120)
insert @tab values(8, 'aa ',168)
insert @tab values(5, 'bb ',159)
insert @tab values(7, 'bb ',179)
insert @tab values(8, 'bb ',149)
insert @tab values(9, 'bb ',139)
insert @tab values(6, 'bb ',169)
select * from @tab
select px=(select count(1) from @tab where b=a.b and c> a.c)+1 , a,b,c from @tab a
order by b , c desc
px a b c
----------- ----------- ---- -----------
1 8 aa 168
2 7 aa 153
3 9 aa 152
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139
（所影响的行数为 9 行）
[解决办法]
create table table5(col1 varchar(02))
insert into table5
select 'a '
insert into table5
select 'a '
insert into table5
select 'b '
insert into table5
select 'b '
insert into table5
select 'c '
insert into table5
select 'c '
insert into table5
select 'c '
select id=identity(int,1,1),col1 into #t from table5 order by col1
select no=(select count(*) from #t b where b.col1=a.col1 and b.id <=a.id),col1
from #t a
drop table #t,table5
no col1
----------- ----
1 a
2 a
1 b
2 b
1 c
2 c
3 c
[解决办法]
--如:
Select *,identity(int,1,1) as ID into #t from (select col= 'a '
union all select 'a '
union all select 'b '
union all select 'b '
union all select 'b '
union all select 'c '
union all select 'c ') as t
Select no=(Select count(*) from #t where col=a.col and id <=a.id),col
from #t as a
drop table #t
[解决办法]
--sql 2005
SELECT ROW_NUMBER() OVER(
PARTITION BY classid
ORDER BY classid asc) AS pos,
classid
FROM table
ORDER BY classid asc
