MSSQl 查询问题

[xp1056]

现有表A如下
自增列 字段A 字段B
1 A1 B1
2 A1 B1
3 A1 B1
4 A1 B2
5 A1 B2
6 A2 B1
7 A2 B1
8 A2 B2
9 A2 B2

想得到如下形式的表B

自增列 字段A 字段B 字段C
1 A1 B1 1
2 A1 B1 2
3 A1 B1 3
4 A1 B2 1
5 A1 B2 2
6 A2 B1 1
7 A2 B1 2
8 A2 B2 1
9 A2 B2 2

相同的字段A 和字段B 的记录按自增列排序

[昵称被占用了]

2000:

select *,字段C=(select count(1) from tb where 自增列<=a.自增列 and 字段A= a.字段A and 字段B= a.字段B)
from tb a
order by 自增列

[水族杰纶]

select *,
      c=(select count(*) from tb where cola=t.cola and colb=t.colb and id>=t.id)
from ta t

[昵称被占用了]

2005+:

select *,字段C=row_number() over (partition by 字段A ,字段B order by 自增列)
from tb
order by 自增列

[水族杰纶]

[Quote=引用 2 楼 wufeng4552 的回复:]
SQL codeselect*,
c=(selectcount(*)from tbwhere cola=t.colaand colb=t.colband id>=t.id)from ta t
[/Quote]

--反了
declare @t table(id int, colA varchar(10),  colB varchar(10))  
insert @t select 1        ,'A1',      'B1' 
insert @t select 2       , 'A1' ,     'B1' 
insert @t select 3      ,  'A1'  ,    'B1' 
insert @t select 4     ,   'A1'   ,   'B2' 
insert @t select 5    ,    'A1'    ,  'B2' 
insert @t select 6   ,     'A2'     , 'B1' 
insert @t select 7  ,      'A2'      ,'B1' 
insert @t select 8 ,       'A2'      ,'B2' 
insert @t select 9,        'A2'      ,'B2' 
select *,
      c=(select count(*) from @t where cola=t.cola and colb=t.colb and id<=t.id)
from @t t
/*
id          colA       colB       c
----------- ---------- ---------- -----------
1           A1         B1         1
2           A1         B1         2
3           A1         B1         3
4           A1         B2         1
5           A1         B2         2
6           A2         B1         1
7           A2         B1         2

8 A2 B2 1
9 A2 B2 2

*/

[昵称被占用了]

[Quote=引用 2 楼 wufeng4552 的回复:]
SQL codeselect*,
c=(selectcount(*)from tbwhere cola=t.colaand colb=t.colband id>=t.id)from ta t
[/Quote]
>=应该是<=吧

[--小F--]

----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date   :2009-08-12 16:28:45
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([自增列] int,[字段A] varchar(2),[字段B] varchar(2))
insert [tb]
select 1,'A1','B1' union all
select 2,'A1','B1' union all
select 3,'A1','B1' union all
select 4,'A1','B2' union all
select 5,'A1','B2' union all
select 6,'A2','B1' union all
select 7,'A2','B1' union all
select 8,'A2','B2' union all
select 9,'A2','B2'
--------------开始查询--------------------------
select 
    *,
    字段C=(select count(1) from tb where 自增列<=t.自增列 and 字段A= t.字段A and 字段B= t.字段B)
from 
   tb t
order by 
自增列
----------------结果----------------------------
/*
自增列         字段A  字段B  字段C         
----------- ---- ---- ----------- 
1           A1   B1   1
2           A1   B1   2
3           A1   B1   3
4           A1   B2   1
5           A1   B2   2
6           A2   B1   1
7           A2   B1   2
8           A2   B2   1
9           A2   B2   2

（所影响的行数为 9 行）

*/

[SQL77]

SELECT *,
(SELECT COUNT(*) FROM TB T WHERE T.A=A AND T.B=B AND ID<=T.ID) AS C 
FROM TB T

?

[--小F--]

[Quote=引用 5 楼 haiwer 的回复:]
引用 2 楼 wufeng4552 的回复:
SQL codeselect*,
      c=(selectcount(*)from tbwhere cola=t.colaand colb=t.colband id>=t.id)from ta t

>=应该是 <=吧

[/Quote]
应该是<= 水哥错误了

[水族杰纶]

[Quote=引用 5 楼 haiwer 的回复:]
引用 2 楼 wufeng4552 的回复:
SQL codeselect*,
      c=(selectcount(*)from tbwhere cola=t.colaand colb=t.colband id>=t.id)from ta t

>=应该是 <=吧

[/Quote]
恩

[lidanzi]

SELECT 自增列,字段A,字段B,row_number() over(partition by 字段A,字段B order by 自增列) as 字段C

[xp1056]

-- create  table   
-- (
-- autoid int identity(1,1)
-- ,Afield varchar(2)
-- ,Bfield varchar(2)
-- )
--创建数据
-- insert into xp1056
-- select  'A2','B2' 
-- select * from xp1056


declare @tb table
(
autoid int 
,Afield varchar(2)
,Bfield varchar(2)
,Cfield int
)
insert into @tb
select autoid,Afield,Bfield,0 from xp1056

select * from @tb
declare  @A varchar(2)
declare  @B varchar(2)
declare  @C int
set @A=''
set @B=''
set @C=1
update @tb 
set 
Cfield=@C
,@C=(case when @A=Afield and @B=Bfield then @C+1 else 1 end)
,@A=Afield
,@B=Bfield

select * 
from @tb
----结果-----
1	A1	B1	1
2	A1	B1	2
3	A1	B1	3
4	A1	B2	1
5	A1	B2	2
6	A2	B1	1
7	A2	B1	2
8	A2	B2	1
9	A2	B2	2