累加问题

[setfocus]

我原来使用如下语句
select distinct T1.A as F1,T2.A as F2,IDENTITY(int,1,1) as F3,2 as F4 into #temp2 from T1,T2 order by T1.A
但是不能出现我想要的如下结果,请高手指点,怎么让这个F3字段值能累加又能在与F1关联改变

F1 F2 F3 F4
------------------------------
A a1 2 2
A a2 2 2
A a3 2 2
B a1 3 2
B a2 3 2
B a3 3 2
C a1 4 2
C a2 4 2
C a3 4 2
D a1 5 2
D a2 5 2
D a3 5 2
-----------------------------

[setfocus]

请帮忙解决

[paoluo]

试试


Select
A.A as F1,
B.A as F2,
(Select Count(Distinct A)+1 from T1 Where A<=A.A) As F3,
2 as F4
into #temp2 from T1 A,T2 B order by A.A

[子陌红尘]

select T1.*,IDENTITY(int,1,1) as F3 into #t

select a.A as F1,b.A as F2,a.F3,2 as F4 from #t a, T2 b order by a.A

[setfocus]

paoluo(一天到晚游泳的鱼):不能增加,我这个T1,T2是两个表,取出的F1的值如果发生改变了才增加,你的语句没能实现!

[paoluo]

--建立测试环境
Create Table TEST1 (A Varchar(10))
Create Table TEST2 (A Varchar(10))
Insert TEST1 Values('A')
Insert TEST1 Values('B')
Insert TEST1 Values('C')
Insert TEST1 Values('D')

Insert TEST2 Values('a1')
Insert TEST2 Values('a2')
Insert TEST2 Values('a3')
--测试
Select
A.A as F1,
B.A as F2,
(Select Count(Distinct A)+1 from TEST1 Where A<=A.A) As F3,
2 as F4
into #temp2
from TEST1 A,TEST2 B order by A.A

Select * from #temp2
--删除测试环境
Drop Table TEST1,TEST2,#temp2
--结果
/*
F1 F2 F3 F4
A a1 2 2
A a2 2 2
A a3 2 2
B a1 3 2
B a2 3 2
B a3 3 2
C a1 4 2
C a2 4 2
C a3 4 2
D a1 5 2
D a2 5 2
D a3 5 2
*/

[bugchen888]

select xx.F1,IDENTITY(int,1,1) as F3
into #t
from (select distinct T1.A as F1 from T1) xx

select a.F1,b.A as F2,a.F3,2 as F4
into #temp
from #t a,T2 b