帮忙看如何写sql语句
table1
number tvalue
111205 abc
111205 cde
111205 oiu
115000 asdf
115000 sadf
115000 e3rer
210000 ert
210000 hrt
485200 kuiui
485200 koko
转成table2
table2
id number tvalue
1 111205 abc
1 111205 cde
1 111205 oiu
2 115000 asdf
2 115000 sadf
2 115000 e3rer
3 210000 ert
3 210000 hrt
4 485200 kuiui
4 485200 koko
大概有一万多条这样的记录,要加id列,并且number相同的id也一样,并且id是连续递增的,如table2
[解决办法]
--sql server 2005
declare @t table(number varchar(10),tvalue varchar(10))
insert into @t select '111205 ', 'abc '
union all select '111205 ', 'cde '
union all select '111205 ', 'oiu '
union all select '115000 ', 'asdf '
union all select '115000 ', 'sadf '
union all select '115000 ', 'e3rer '
union all select '210000 ', 'ert '
union all select '210000 ', 'hrt '
union all select '485200 ', 'kuiui '
union all select '485200 ', 'koko '
select dense_rank() over(order by number) as id,* from @t
/*
id number tvalue
-------------------- ---------- ----------
1 111205 abc
1 111205 cde
1 111205 oiu
2 115000 asdf
2 115000 sadf
2 115000 e3rer
3 210000 ert
3 210000 hrt
4 485200 kuiui
4 485200 koko
*/
[解决办法]
ACCESS中這麼寫
Select
(Select Count(number) From (Select Distinct number From T1) B Where B.number <= A.number ) As ID,
number,
tvalue
From
T1 A
