求一SQL 指令
有一表如下 ：
create table tab(col1 char(10),col2 char(10))
insert tab
select 'A ', 'a1 '
union all select 'B ', 'a2 '
union all select 'B ', 'a3 '
union all select 'B ', 'a4 '
union all select 'C ', 'a5 '
union all select 'C ', 'a6 '
union all select 'D ', 'a7 '
想得到如下的结果：
-col1----col2------col3--
Aa1 0001
Ba2 0001
Ba3 0002
Ba4 0003
Ca5 0001
Ca6 0002
Da7 0001
[解决办法]
create table tab(col1 char(10),col2 char(10))
insert tab
select 'A ', 'a1 '
union all select 'B ', 'a2 '
union all select 'B ', 'a3 '
union all select 'B ', 'a4 '
union all select 'C ', 'a5 '
union all select 'C ', 'a6 '
union all select 'D ', 'a7 '
GO
Select *, Right(10000 + (Select Count(col1) From tab Where col1 = A.col1 And col2 <= A.col2), 4) As col3 From tab A
GO
Drop Table tab
--Result
/*
Col1Col2Col3
A a1 0001
B a2 0001
B a3 0002
B a4 0003
C a5 0001
C a6 0002
D a7 0001
*/
