select id,username,pass from (select *,px=(select count(1)+1 from tb where id>t.id) from tb t) tt where px/2=0
修正一下---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[username] int,[pass] int) insert [tb] select 22,22,22 union all select 21,21,21 union all select 18,18,18 union all select 15,15,15 union all select 14,14,14 union all select 10,10,10 union all select 8,8,8 union all select 7,7,7 union all select 6,6,6 union all select 2,2,2 union all select 1,1,1
---查询--- select id,username,pass from (select *,px=(select count(1)+1 from tb where id<t.id) from tb t) tt where px%3=1---结果--- id username pass ----------- ----------- ----------- 21 21 21 14 14 14 7 7 7 1 1 1(所影响的行数为 4 行)
--sql 2000create table [tb]([id] int,[username] int,[pass] int) insert [tb] select 22,22,22 union all select 21,21,21 union all select 18,18,18 union all select 15,15,15 union all select 14,14,14 union all select 10,10,10 union all select 8,8,8 union all select 7,7,7 union all select 6,6,6 union all select 2,2,2 union all select 1,1,1select id ,username,pass from ( select * , px = (select count(1) from tb where id > t.id) + 1 from tb t ) m where px % 3 = 2drop table tb/* id username pass ----------- ----------- ----------- 21 21 21 14 14 14 7 7 7 1 1 1(所影响的行数为 4 行) */
--2005create table [tb]([id] int,[username] int,[pass] int) insert [tb] select 22,22,22 union all select 21,21,21 union all select 18,18,18 union all select 15,15,15 union all select 14,14,14 union all select 10,10,10 union all select 8,8,8 union all select 7,7,7 union all select 6,6,6 union all select 2,2,2 union all select 1,1,1select id ,username,pass from ( select * , px = row_number() over(order by id desc) from tb t ) m where px % 3 = 2drop table tb/* id username pass ----------- ----------- ----------- 21 21 21 14 14 14 7 7 7 1 1 1(4 行受影响) */
create table tb([id] int,username int,pass int) insert into tb values(22,22,22) insert into tb values(21,21,21) insert into tb values(18,18,18) insert into tb values(15,15,15) insert into tb values(14,14,14) insert into tb values(10,10,10) insert into tb values(8,8,8) insert into tb values(7,7,7) insert into tb values(6,6,6) insert into tb values(2,2,2) insert into tb values(1,1,1)select * from tb t where (select count(tb.id) from tb where id>t.id)%3=1/* id username pass ----------- ----------- ----------- 21 21 21 14 14 14 7 7 7 1 1 1*/drop table tb
create table tb([id] int,username int,pass int) insert into tb values(22,22,22) insert into tb values(21,21,21) insert into tb values(18,18,18) insert into tb values(15,15,15) insert into tb values(14,14,14) insert into tb values(10,10,10) insert into tb values(8,8,8) insert into tb values(7,7,7) insert into tb values(6,6,6) insert into tb values(2,2,2) insert into tb values(1,1,1) select * from tb t where (select count(tb.id) from tb where id>t.id)%3=1 /* id username pass ----------- ----------- ----------- 21 21 21 14 14 14 7 7 7 1 1 1 */ drop table tb
id,username,pass
from
(select *,px=(select count(1)+1 from tb where id>t.id) from tb t) tt
where px/2=0
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[username] int,[pass] int)
insert [tb]
select 22,22,22 union all
select 21,21,21 union all
select 18,18,18 union all
select 15,15,15 union all
select 14,14,14 union all
select 10,10,10 union all
select 8,8,8 union all
select 7,7,7 union all
select 6,6,6 union all
select 2,2,2 union all
select 1,1,1
---查询---
select
id,username,pass
from
(select *,px=(select count(1)+1 from tb where id<t.id) from tb t) tt
where px%3=1---结果---
id username pass
----------- ----------- -----------
21 21 21
14 14 14
7 7 7
1 1 1(所影响的行数为 4 行)
insert [tb]
select 22,22,22 union all
select 21,21,21 union all
select 18,18,18 union all
select 15,15,15 union all
select 14,14,14 union all
select 10,10,10 union all
select 8,8,8 union all
select 7,7,7 union all
select 6,6,6 union all
select 2,2,2 union all
select 1,1,1select id ,username,pass from
(
select * , px = (select count(1) from tb where id > t.id) + 1 from tb t
) m
where px % 3 = 2drop table tb/*
id username pass
----------- ----------- -----------
21 21 21
14 14 14
7 7 7
1 1 1(所影响的行数为 4 行)
*/
insert [tb]
select 22,22,22 union all
select 21,21,21 union all
select 18,18,18 union all
select 15,15,15 union all
select 14,14,14 union all
select 10,10,10 union all
select 8,8,8 union all
select 7,7,7 union all
select 6,6,6 union all
select 2,2,2 union all
select 1,1,1select id ,username,pass from
(
select * , px = row_number() over(order by id desc) from tb t
) m
where px % 3 = 2drop table tb/*
id username pass
----------- ----------- -----------
21 21 21
14 14 14
7 7 7
1 1 1(4 行受影响)
*/
insert into tb values(22,22,22)
insert into tb values(21,21,21)
insert into tb values(18,18,18)
insert into tb values(15,15,15)
insert into tb values(14,14,14)
insert into tb values(10,10,10)
insert into tb values(8,8,8)
insert into tb values(7,7,7)
insert into tb values(6,6,6)
insert into tb values(2,2,2)
insert into tb values(1,1,1)select * from tb t where (select count(tb.id) from tb where id>t.id)%3=1/*
id username pass
----------- ----------- -----------
21 21 21
14 14 14
7 7 7
1 1 1*/drop table tb
insert into tb values(22,22,22)
insert into tb values(21,21,21)
insert into tb values(18,18,18)
insert into tb values(15,15,15)
insert into tb values(14,14,14)
insert into tb values(10,10,10)
insert into tb values(8,8,8)
insert into tb values(7,7,7)
insert into tb values(6,6,6)
insert into tb values(2,2,2)
insert into tb values(1,1,1) select * from tb t where (select count(tb.id) from tb where id>t.id)%3=1 /*
id username pass
----------- ----------- -----------
21 21 21
14 14 14
7 7 7
1 1 1 */ drop table tb