有一表
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 8 aa 168
2 9 aa 152
3 7 aa 153
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139
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 8 aa 168
2 9 aa 152
3 7 aa 153
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139
order by b asc,c asc
漏了点东西select identity(int,1,1) px,a,b,c into 另一个表 from 表
order by b asc,c asc
insert @t1 values('7','aa',153)
insert @t1 values('9','aa',152)
insert @t1 values('6','aa',120)
insert @t1 values('8','aa',168)
insert @t1 values('5','bb',159)
insert @t1 values('7','bb',179)
insert @t1 values('8','bb',149)
insert @t1 values('9','bb',139)
insert @t1 values('6','bb',169)select fx=(select count(*) from (select * from @t1 as r where r.b =q.b) t where t.c>q.c)+1
, a, b,c from @t1 as q order by q.b, c desc
select fx=(select count(*) from (select * from @t1 as r where r.b =q.b) t where t.c>q.c)+1
, a, b,c from @t1 as q order by q.b, c desc
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 @tabselect 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 descpx 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 行)