如题,谢谢
有如下表a的数据(表内允许有重复数据,因为还有其他字段)
id1         je
1379       1000
1379       2000
1379       3000
1379       2000
1400       1200
1400       1300
得出如下表:
id1       idd       je
1379       1         1000
1379       2         2000
1379       3         3000
1379       4         2000
1400       1         1200
1400       2         1300


------解决方案--------------------
循环可以做..感觉不应该这样麻烦吧..期待高手
------解决方案--------------------
create table 表a(id1 int,je int,k int)
go
insert into 表a select 1379,1000,1
union select 1379,2000,2
union select 1379,3000,3
union select 1379,2000,4
union select 1400,1200,5
union select 1400,1300,6
select *,idd=0 into # from 表a order by id1 asc
declare @a int,@tmp int
update #
set @tmp=case when id1=@a
then isnull(@tmp,0)+1
else 1 end,

@a=id1,
idd=@tmp
select * from #
drop table 表a
drop table #

-------------
1379 1000 1 1
1379 2000 2 2
1379 2000 4 3
1379 3000 3 4
1400 1200 5 1
1400 1300 6 2
------解决方案--------------------

--沒有主鍵，借用臨時表吧。
Select id = Identity(Int, 1, 1), * Into #T From a
Select id1, idd = (Select Count(id) From #T Where id1 = A.id1 And id <= A.id), je From #T A
Drop Table #T
------解决方案--------------------
create table #t(id1 int,je int)
go
insert into #t select 1379,1000
union select 1379,2000
union select 1379,3000
union select 1379,2000
union select 1400,1200
union select 1400,1300


select id1,
id=(select count(*) from #t where id1=a.id1 and je <=a.je),
je
from #t a

id1 id je
----------- ----------- -----------
1379 1 1000
1379 2 2000
1379 3 3000
1400 1 1200
1400 2 1300

(5 row(s) affected)