楼主,各位同胞,为什么我用下面的句子(itblog的)结果就不是想要的呢? 顺便up
select ID,[name],sex,第几位=(select count(*) from #t where [name]=a.[name] and sex=a.sex and id<a.id)+1 from #t a order by id
---------显示
id name sex 第几位
1 天仙美眉 0 1
2 芙蓉姐姐 0 1
3 姚明 1 1
4 大致 1 1
declare @t table([id] int,[name] varchar(20),sex bit)
insert into @t select 1,'天仙美眉','0'
union all select 2,'芙蓉姐姐','0'
union all select 3,'姚明','1'
union all select 4,'大致','1'
select ID,[name],sex,第几位=(select count(*) from @t where sex=a.sex and id<a.id)+1 from @t a order by id
create table #t([id] int,[name] varchar(20),sex bit)
insert into #t select 1,'天仙美眉','0'
union all select 2,'芙蓉姐姐','0'
union all select 3,'姚明','1'
union all select 4,'大致','1'
go
declare @name varchar(10)
set @name='芙蓉姐姐'
select *,次序=
(case [name] when @name then (select count(*) from #t where [id] <=(select [id] from #t where [name]=@name))else null end)
from #t where sex=0