select * from ( select *,ROW_NUMBER () over (order by codeid) id from a) tb1 where tb1.id=25
SQL2000: select *,IDENTity(int,1,1) as ID into #a from 你的表名 select * from #a where #a.ID=25 SQL2005: select * from ( select *,ROW_NUMBER () over (order by codeid) id from a) tb1 where tb1.id=25
SQL2000: select *,IDENTity(int,1,1) as ID into #a from 你的表名 select * from #a where #a.ID=25 SQL2005: select * from ( select *,ROW_NUMBER () over (order by codeid) id from a) tb1 where tb1.id=25
不是我要的效果例如说下面是表列 a b c 字段xx SS AA 字段WW RR YY 字段QQ VV NN ... (最中间的一条)子段 HH RR PP ....如何查处那个最中间的一条??
If not object_id('[d]') is null Drop table [d] Go Create table [d](col varchar(10)) Insert d Select 'a' union all Select 'a' union all Select 'b' union all Select 'c' union all Select 'd' union all Select 'e' Go ;with t as ( select rn=row_number()over(order by getdate()),* from d ) select * from t where rn=(select max(rn)/2 from t) /* rn col -------------------- ---------- 3 b(1 行受影响) */
select *,IDENTity(int,1,1) as ID into #a from 你的表名 select * from #a where ID=(SELECT MAX(ID)/2 FROM #a )
select * from (
select *,ROW_NUMBER () over (order by codeid) id from a) tb1
where tb1.id=25
select *,IDENTity(int,1,1) as ID into #a from 你的表名
select * from #a
where #a.ID=25
SQL2005:
select * from (
select *,ROW_NUMBER () over (order by codeid) id from a) tb1
where tb1.id=25
SQL2000:
select *,IDENTity(int,1,1) as ID into #a from 你的表名
select * from #a
where #a.ID=25
SQL2005:
select * from (
select *,ROW_NUMBER () over (order by codeid) id from a) tb1
where tb1.id=25
字段xx SS AA
字段WW RR YY
字段QQ VV NN
...
(最中间的一条)子段 HH RR PP
....如何查处那个最中间的一条??
Drop table [d]
Go
Create table [d](col varchar(10))
Insert d
Select 'a' union all
Select 'a' union all
Select 'b' union all
Select 'c' union all
Select 'd' union all
Select 'e'
Go
;with t as
(
select rn=row_number()over(order by getdate()),* from d
)
select * from t
where rn=(select max(rn)/2 from t)
/*
rn col
-------------------- ----------
3 b(1 行受影响)
*/
select *,IDENTity(int,1,1) as ID into #a from 你的表名
select * from #a
where ID=(SELECT MAX(ID)/2 FROM #a )