例如: select * from table 结果如下: ID Title
29 AAA
30 AA2
31 AA3
89 BBB
90 BBB
90 BBB
99 CCC我现在需要的结果 select * from table where Title = 'BBB'
ID Title 编号
89 BBB 1
90 BBB 2
90 BBB 3如果使用以下代码,编号就会是:3、4、5
select
*,
编号=(select count(1)+1 from tb where id<t.id)
from tb t
29 AAA
30 AA2
31 AA3
89 BBB
90 BBB
90 BBB
99 CCC我现在需要的结果 select * from table where Title = 'BBB'
ID Title 编号
89 BBB 1
90 BBB 2
90 BBB 3如果使用以下代码,编号就会是:3、4、5
select
*,
编号=(select count(1)+1 from tb where id<t.id)
from tb t
*,
编号=(select count(1)+1 from tb where title=t.title and id <t.id)
from tb t where title='BBB'
表的iD = 90 的怎么是两个?create table #1(id int,title nvarchar(50))
insert #1 values(29,'AAA')
insert #1 values(30 ,'AA2')
insert #1 values(31 ,'AA3')
insert #1 values(89 ,'BBB')
insert #1 values(90 ,'BBB')
insert #1 values(90 ,'BBB')
insert #1 values(99, 'CCC') select
*,
编号=(select count(1)+1 from #1 where id <t.id)
from #1 tid title 编号
----------- -------------------------------------------------- -----------
29 AAA 1
30 AA2 2
31 AA3 3
89 BBB 4
90 BBB 5
90 BBB 5
99 CCC 7(7 row(s) affected)select *
,编号 = (select count(1)+1 from #1 where id <t.id and title = t.title)
from #1 t
where title ='BBB'id title 编号
----------- -------------------------------------------------- -----------
89 BBB 1
90 BBB 2
90 BBB 2(3 row(s) affected)
ID + Title 有完全重复的?
用2005的row_number()select * , 编号 = row_number() over(order by id , title) from tb where title = 'BBB'如果是2000
需要使用临时表
select * , px = identity(int,1,1) into tmp from tb order by id , title
select id , title , 编号 = (select count(*) from tmp where id < t.id or (id = t.id and px < t.px)) + 1 from tmp t
*,
编号=(select count(1)+1 from tb where title=t.title and id <t.id)
from tb t where title='BBB'
declare @tb table (id int,title nvarchar(10))
insert into @tb select 29,'AAA'
union all select 30,'AA2'
union all select 31,'AA3'
union all select 89,'BBB'
union all select 90,'BBB'
union all select 99,'BBB'
select *,ROW_NUMBER()over(order by id) 编号 from @tb
where title='BBB'id title 编号
----------- ---------- --------------------
89 BBB 1
90 BBB 2
99 BBB 3(3 行受影响)