表T1有以下记录:ID F1 F2
-----------------------------------
2561 AA ASDFJ
2562 AA KLJIUO
2563 AA KLKJOIU
2564 BB UIWOE
2565 BB FSAFHFAS视图V1结果:AUTOID ID F1 F2
---------------------------------------------
1 2561 AA ASDFJ
2 2562 AA KLJIUO
3 2563 AA KLKJOIU
1 2564 BB UIWOE
2 2565 BB FSAFHFAS在sql server 2000环境下,不允许用row_number(),生成autoid,请问这个view如何写?
-----------------------------------
2561 AA ASDFJ
2562 AA KLJIUO
2563 AA KLKJOIU
2564 BB UIWOE
2565 BB FSAFHFAS视图V1结果:AUTOID ID F1 F2
---------------------------------------------
1 2561 AA ASDFJ
2 2562 AA KLJIUO
3 2563 AA KLKJOIU
1 2564 BB UIWOE
2 2565 BB FSAFHFAS在sql server 2000环境下,不允许用row_number(),生成autoid,请问这个view如何写?
(select count(1) from tb where id<=t.id) atuoid
drop table tb
Go
Create table tb([ID] Datetime,[F1] nvarchar(2),[F2] nvarchar(8))
Insert tb
select '2561',N'AA',N'ASDFJ' union all
select '2562',N'AA',N'KLJIUO' union all
select '2563',N'AA',N'KLKJOIU' union all
select '2564',N'BB',N'UIWOE' union all
select '2565',N'BB',N'FSAFHFAS'
Go
Select
(select count(*) from tb where [F1]=t.[F1] and [ID]<=t.[ID])AUTOID,
*
from tb t
/*
AUTOID ID F1 F2
----------- ----------------------- ---- --------
1 2561-01-01 00:00:00.000 AA ASDFJ
2 2562-01-01 00:00:00.000 AA KLJIUO
3 2563-01-01 00:00:00.000 AA KLKJOIU
1 2564-01-01 00:00:00.000 BB UIWOE
2 2565-01-01 00:00:00.000 BB FSAFHFAS*/
(select count(*) from tb where [F1]=t.[F1] and [ID]<=t.[ID])AUTOID,
*
from tb t
group by atuoid,f1,f2...