SELECT *,(SELECT COUNT(1) FROM TB WHERE ID<=T.ID) AS NUM FROM TB T
select row_number = 1 + isnull((select count(*) from t where t.id < d.id),0), * from t d where d.ColA is null order by d.id
select min(no) from (select row_number() over(order by getdate()) no,* from tb) a where cola is null
稍修改一下 SELECT *,(SELECT COUNT(1) FROM TB WHERE ID<=T.ID) AS RowNUM FROM TB T WHERE COLA IS NULL
declare @table table (ID int,ColA varchar(2)) insert into @table select 57,'AD' union all select 58,'AC' union all select 59,'AY' union all select 60,null union all select 61,null union all select 62,null union all select 63,null union all select 64,null union all select 65,null union all select 66,nullselect min(ID) from @table where ColA is null /*60*/
补充一下,只是要获取为从第几行,开始为Null,返回行号就好
declare @table table (ID int,ColA varchar(2)) insert into @table select 57,'AD' union all select 58,'AC' union all select 59,'AY' union all select 60,null union all select 61,null union all select 62,null union all select 63,null union all select 64,null union all select 65,null union all select 66,nullselect min(no) from (select row_number() over(order by getdate()) no,* from @table) a where cola is null /* -------------------- 4
结贴了,感谢@ssp2009 和大家的帮助
create table tb(ID int,ColA varchar(2)) insert into tb select 57,'AD' union all select 58,'AC' union all select 59,'AY' union all select 60,null union all select 61,null union all select 62,null union all select 63,null union all select 64,null union all select 65,null union all select 66,nullselect (select count(1)+1 from tb b where b.id<a.id) from tb a where a.id=(select min(id) from tb where ColA is null)/*
* from t d
where d.ColA is null
order by d.id
(select row_number() over(order by getdate()) no,* from tb) a
where cola is null
稍修改一下
SELECT *,(SELECT COUNT(1) FROM TB WHERE ID<=T.ID) AS RowNUM FROM TB T WHERE COLA IS NULL
declare @table table (ID int,ColA varchar(2))
insert into @table
select 57,'AD' union all
select 58,'AC' union all
select 59,'AY' union all
select 60,null union all
select 61,null union all
select 62,null union all
select 63,null union all
select 64,null union all
select 65,null union all
select 66,nullselect min(ID) from @table where ColA is null
/*60*/
insert into @table
select 57,'AD' union all
select 58,'AC' union all
select 59,'AY' union all
select 60,null union all
select 61,null union all
select 62,null union all
select 63,null union all
select 64,null union all
select 65,null union all
select 66,nullselect min(no) from
(select row_number() over(order by getdate()) no,* from @table) a
where cola is null
/*
--------------------
4
create table tb(ID int,ColA varchar(2))
insert into tb
select 57,'AD' union all
select 58,'AC' union all
select 59,'AY' union all
select 60,null union all
select 61,null union all
select 62,null union all
select 63,null union all
select 64,null union all
select 65,null union all
select 66,nullselect (select count(1)+1 from tb b where b.id<a.id) from tb a
where a.id=(select min(id) from tb where ColA is null)/*
-----------
4(所影响的行数为 1 行)
*/