34,837
社区成员




A A1
a 1
a1 1
a2 2
a3 4
要得到,每个A1自动加所在的行数据.即:
A A1
a 2
a1 3
a2 5
a3 8
--如果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(*)