如题,谢谢
有如下表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 #
-------------
1379100011
1379200022
1379200043
1379300034
1400120051
1400130062
[解决办法]
--沒有主鍵，借用臨時表吧。
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)
