如表1
id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
以最末行15为准,分别统计间隔1、2、3....的表情况。得到如下结果:
间隔1行:
id
1
3
5
7
9
11
13
15
间隔2行:
id
3
6
9
12
15
间隔3行:
id
3
7
11
15
.......
id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
以最末行15为准,分别统计间隔1、2、3....的表情况。得到如下结果:
间隔1行:
id
1
3
5
7
9
11
13
15
间隔2行:
id
3
6
9
12
15
间隔3行:
id
3
7
11
15
.......
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int)
insert into #
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15 --间隔1
select * from # where (id-1)%2=0
/*
id
-----------
1
3
5
7
9
11
13
15(8 行受影响)*/--间隔2
select * from # where id%3=0
/*
id
-----------
3
6
9
12
15(5 行受影响)*/select * from # where (id+1)%4=0 --间隔3
/*
id
-----------
3
7
11
15(4 行受影响)*/
drop table #
create table #( a int )
declare @i int
set @i=1
while (@i<16 )
begin
insert into #(a) select @i
set @i=@i+1
end
select * from # where 1=a%2
select * from # where 0=a%3
select * from # where 0=a%4
drop table #a
-----------
1
3
5
7
9
11
13
15(8 行受影响)a
-----------
3
6
9
12
15(5 行受影响)a
-----------
4
8
12(3 行受影响)
create table tb(id int)
insert into tb
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15--1、如果ID顺序增加,且不间断,用取模的方式即可。
select * from tb where id%2 = 1
/*
id
-----------
1
3
5
7
9
11
13
15(所影响的行数为 8 行)
*/select * from tb where id%3 = 1
/*
id
-----------
1
4
7
10
13(所影响的行数为 5 行)
*/select * from tb where id%4 = 1
/*
id
-----------
1
5
9
13(所影响的行数为 4 行)*/--2、如果ID不是顺序增加,且存在间断,用子查询生成不间断的序号,然后再取模的方式即可。
select id from (select * ,px = (select count(1) from tb where id < t.id) + 1 from tb t) m where px % 2 = 1
/*
id
-----------
1
3
5
7
9
11
13
15(所影响的行数为 8 行)
*/
select id from (select * ,px = (select count(1) from tb where id < t.id) + 1 from tb t) m where px % 3 = 1
/*
id
-----------
1
4
7
10
13(所影响的行数为 5 行)
*/select id from (select * ,px = (select count(1) from tb where id < t.id) + 1 from tb t) m where px % 4 = 1
/*
id
-----------
1
5
9
13(所影响的行数为 4 行)
*/drop table tb
create table tb(id int)
insert into tb
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10 union all
select 11 union all
select 12 union all
select 13 union all
select 14 union all
select 15--1、如果ID顺序增加,且不间断,用取模的方式即可。和sql 2000一样。
select * from tb where id%2 = 1
/*
id
-----------
1
3
5
7
9
11
13
15(8 行受影响)
*/select * from tb where id%3 = 1
/*
id
-----------
1
4
7
10
13(5 行受影响)
*/select * from tb where id%4 = 1
/*
id
-----------
1
5
9
13(4 行受影响)
*/--2、如果ID不是顺序增加,且存在间断,用row_number()生成不间断的序号,然后再取模的方式即可。
select id from (select * ,px = row_number() over(order by id) from tb t) m where px % 2 = 1
/*
id
-----------
1
3
5
7
9
11
13
15(8 行受影响)
*/
select id from (select * ,px = row_number() over(order by id) from tb t) m where px % 3 = 1
/*
id
-----------
1
4
7
10
13(5 行受影响)
*/select id from (select * ,px = row_number() over(order by id) from tb t) m where px % 4 = 1
/*
id
-----------
1
5
9
13(4 行受影响)*/drop table tb
select id from sometable
where mod(id, x) = mod(15, x)x=1,2,3,...