---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-07-27 10:41:24---------------------------------------------------------------- -- Author :fredrickhu(小F 向高手学习) -- Date :2009-07-27 11:53:06 ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] create table [tb]([aa] varchar(1)) insert [tb] select 'a' union all select 'a' union all select 'a' union all select 'a' union all select 'b' union all select 'b' union all select 'b' union all select 'b' --------------开始查询-------------------------- alter table tb add id int identity go select id=(select count(1)+1 from tb where aa=t.aa and [id]<t.[id]),aa from tb t ----------------结果----------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-27 10:41:24----------------------------------------------------------------
-- Author :fredrickhu(小F 向高手学习)
-- Date :2009-07-27 11:53:06
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb]([aa] varchar(1))
insert [tb]
select 'a' union all
select 'a' union all
select 'a' union all
select 'a' union all
select 'b' union all
select 'b' union all
select 'b' union all
select 'b'
--------------开始查询--------------------------
alter table tb
add id int identity
go
select id=(select count(1)+1 from tb where aa=t.aa and [id]<t.[id]),aa from tb t
----------------结果----------------------------
-----------------------------
有的有,有的无,
sql2000无.