sql 语句的问题

--如果A列不能区分大小，只能使用临时表

create table tb(A varchar(10) ,  A1 int)   

insert into tb values('a' ,  1)

insert into tb values('a1',  1)

insert into tb values('a2',  2)

insert into tb values('a3',  4)

go



select * , px = identity(int,1,1) into tmp from tb



select A , A1 = A1 + px from tmp



drop table tb,tmp



/*

A          A1          

---------- ----------- 

a          2

a1         3

a2         5

a3         8



（所影响的行数为 4 行）

*/

select A, A1 + (select count(1) from tableA where A <= tbA.A)

from tableA tbA

create table tb(A varchar(10) ,  A1 int)   

insert into tb values('a' ,  1)

insert into tb values('a1',  1)

insert into tb values('a2',  2)

insert into tb values('a3',  4)

go



select A , A1 = A1 + px from

(

  select * , px = (select count(1) from tb where A < t.A) + 1 from tb t

) m



drop table tb



/*

A          A1          

---------- ----------- 

a          2

a1         3

a2         5

a3         8



（所影响的行数为 4 行）

*/

declare @T table (col1 varchar(10),col2 int)

insert @T values('a',1)

insert @T values('a1',1)

insert @T values('a2',2)

insert @T values('a3',4)



select *,col3=(select sum(col2) from @T where col1<=A.col1)

from @T A



/*

col1       col2        col3

---------- ----------- -----------

a          1           1

a1         1           2

a2         2           4

a3         4           8



(4 行受影响)



*/

select A , A1 = A1 + px from

(

  select * , px = (select count(1) from tb where A < t.A) + 1 from tb t

) m

declare @tb Table(nid int identity(1,1),num int )



insert @tb select 1

union all select 3

union all select 4

union all select 8

union all select 16

union all select 87



select * from @tb

/*

1	1

2	3

3	4

4	8

5	16

6	87

*/



select nid, num=num+ncol from 

(

  select *,ncol=(select count(*) from @tb  where nid<=m.nid)from @tb m

) n

 select nid, num=num+ncol from 

(

  select *,ncol=(select count(*) from @tb m where m.nid<=nid)from @tb  

) n

--错误结果，算出的ncol=count（*）