declare @t table(a varchar(20)) insert @t select 'a,5,5' union all select 'b,5,5' union all select 'c,5,5'declare @I int set @I =0update @t set a = convert(varchar(2),@i)+','+a ,@i =@i +1select * from @t/*a -------------------- 1,a,5,5 2,b,5,5 3,c,5,5 */
declare @t table(col1 varchar(10),col2 int,col3 int) insert into @t select 'a',5,5 union all select 'b',5,5 union all select 'c',5,5select 序号=(select sum(1) from @t where col1<=A.col1), * from @t A--严格一点 select 序号=(select sum(1) from @t where col1+'_'+cast(col2 as varchar)+'_'+cast(col3 as varchar)<=A.col1+'_'+cast(A.col2 as varchar)+'_'+cast(A.col3 as varchar)), * from @t A序号 col1 col2 col3 ----------- ---------- ----------- ----------- 1 a 5 5 2 b 5 5 3 c 5 5(所影响的行数为 3 行)序号 col1 col2 col3 ----------- ---------- ----------- ----------- 1 a 5 5 2 b 5 5 3 c 5 5(所影响的行数为 3 行) --关键找准你表的主键字段。
into #tmp
from testselect * from #tmpdrop table #tmp
a,5,5
b,5,5
c,5,5
====>
1,a,5,5
2,b,5,5
3,c,5,5没分了,谢谢各位大哥先
insert @t
select 'a,5,5' union all
select 'b,5,5' union all
select 'c,5,5'declare @I int
set @I =0update @t set a = convert(varchar(2),@i)+','+a ,@i =@i +1select * from @t/*a
--------------------
1,a,5,5
2,b,5,5
3,c,5,5
*/
insert into @t select 'a',5,5
union all select 'b',5,5
union all select 'c',5,5select
序号=(select sum(1) from @t where col1<=A.col1),
*
from @t A--严格一点
select
序号=(select sum(1) from @t where col1+'_'+cast(col2 as varchar)+'_'+cast(col3 as varchar)<=A.col1+'_'+cast(A.col2 as varchar)+'_'+cast(A.col3 as varchar)),
*
from @t A序号 col1 col2 col3
----------- ---------- ----------- -----------
1 a 5 5
2 b 5 5
3 c 5 5(所影响的行数为 3 行)序号 col1 col2 col3
----------- ---------- ----------- -----------
1 a 5 5
2 b 5 5
3 c 5 5(所影响的行数为 3 行)
--关键找准你表的主键字段。