declare @t table(id int,loclevel int,no int)
insert into @t select 1,1,1
insert into @t select 2,1,2
insert into @t select 3,1,4
insert into @t select 4,1,5
insert into @t select 5,1,6
insert into @t select 6,2,1
insert into @t select 7,2,2
insert into @t select 8,2,3
insert into @t select 9,2,4 select
a.*
from
@t a,@t b,@t c
where
a.id=b.id-1 and a.no=b.no-1 and a.loclevel=b.loclevel
and
a.id=c.id-2 and a.no=c.no-2 and a.loclevel=c.loclevel/*
id loclevel no
----------- ----------- -----------
3 1 4
6 2 1
7 2 2
*/
insert into @t select 1,1,1
insert into @t select 2,1,2
insert into @t select 3,1,4
insert into @t select 4,1,5
insert into @t select 5,1,6
insert into @t select 6,2,1
insert into @t select 7,2,2
insert into @t select 8,2,3
insert into @t select 9,2,4 select
a.*
from
@t a,@t b,@t c
where
a.id=b.id-1 and a.no=b.no-1 and a.loclevel=b.loclevel
and
a.id=c.id-2 and a.no=c.no-2 and a.loclevel=c.loclevel/*
id loclevel no
----------- ----------- -----------
3 1 4
6 2 1
7 2 2
*/
insert into @t select 1,1,1
insert into @t select 2,1,2
insert into @t select 3,1,4
insert into @t select 4,1,5
insert into @t select 5,1,6
insert into @t select 6,2,1
insert into @t select 7,2,2
insert into @t select 8,2,3
insert into @t select 9,2,4 select
a.*
from
@t a
where
exists(select 1 from @t where a.id=id-1 and a.no=no-1 and a.loclevel=loclevel)
and
exists(select 1 from @t where a.id=id-2 and a.no=no-2 and a.loclevel=loclevel)/*
id loclevel no
----------- ----------- -----------
3 1 4
6 2 1
7 2 2
*/
declare @s table (id int,loclevel int,no int)
insert into @s
select 1,1,1 union all
select 2,1,2 union all
select 3,1,4 union all
select 4,1,5 union all
select 5,1,6 union all
select 6,2,1 union all
select 7,2,2 union all
select 8,2,3 union all
select 9,2,4select * from @s a
where exists(select 1 from @s where loclevel=a.loclevel and no=a.no+1)
and exists(select 1 from @s where loclevel=a.loclevel and no=a.no+2)--结果:
id loclevel no
----------- ----------- -----------
3 1 4
6 2 1
7 2 2
insert into @t select 1,1,1
insert into @t select 2,1,2
insert into @t select 3,1,4
insert into @t select 4,1,5
insert into @t select 5,1,6
insert into @t select 6,2,1
insert into @t select 7,2,2
insert into @t select 8,2,3
insert into @t select 9,2,4 declare @i int
set @i=3 --当N值变化时,只需替换此处的@i值select
a.*
from
@t a,
(select t.* from @t t where not exists(select 1 from @t where id=t.id+1 and loclevel=t.loclevel and no=t.no+1)) b
where
a.id<=b.id and a.loclevel=b.loclevel
group by
a.id,a.loclevel,a.no
having
min(b.no)-a.no>=@i-1
/*
id loclevel no
----------- ----------- -----------
3 1 4
6 2 1
7 2 2
*/
declare @s table (id int,loclevel int,no int)
insert into @s
select 1,1,1 union all
select 2,1,2 union all
select 3,1,4 union all
select 4,1,5 union all
select 5,1,6 union all
select 6,2,1 union all
select 7,2,2 union all
select 8,2,3 union all
select 9,2,4select * from @s a
where exists(select 1 from @s where loclevel=a.loclevel and no=a.no+1)
and exists(select 1 from @s where loclevel=a.loclevel and no=a.no+2)--结果:
id loclevel no
----------- ----------- -----------
3 1 4
6 2 1
7 2 2
--> 测试时间:2009-07-09 16:18:21
--> 我的淘宝: http://shop36766744.taobao.com/if object_id('[tab]') is not null drop table [tab]
create table [tab]([id] int,[loclevel] int,[no] int)
insert [tab]
select 1,1,1 union all
select 2,1,2 union all
select 3,1,4 union all
select 4,1,5 union all
select 5,1,6 union all
select 6,2,1 union all
select 7,2,2 union all
select 8,2,3 union all
select 9,2,4select * from tab a where [no]=(select [no] from tab where a.loclevel=loclevel and ID=a.ID+2)-2/*
id loclevel no
----------- ----------- -----------
3 1 4
6 2 1
7 2 2(所影响的行数为 3 行)*/
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(id int,loclevel int ,no int)
go
insert into tb select 1,1,1
insert into tb select 2,1,2
insert into tb select 3,1,4
insert into tb select 4,1,5
insert into tb select 5,1,6
insert into tb select 6,2,1
insert into tb select 7,2,2
insert into tb select 8,2,3
insert into tb select 9,2,4
go
declare @s int
set @s=3--可以任意改
select * from tb a
where [no]=(select [no] from tb where a.loclevel=loclevel and ID=a.ID+@s-1)-@s+1
/*------------
3 1 4
6 2 1
7 2 2
-------*/
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-09 16:18:37
-------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (id INT,loclevel INT,no INT)
INSERT INTO @T
SELECT 1,1,1 UNION ALL
SELECT 2,1,2 UNION ALL
SELECT 3,1,4 UNION ALL
SELECT 4,1,5 UNION ALL
SELECT 5,1,6 UNION ALL
SELECT 6,2,1 UNION ALL
SELECT 7,2,2 UNION ALL
SELECT 8,2,3 UNION ALL
SELECT 9,2,4--SQL查询如下:DECLARE @n INT;
SET @n = 3;;WITH Liang AS
(
SELECT value_no=ROW_NUMBER() OVER(PARTITION BY loclevel ORDER BY id)-no,
rowid=ROW_NUMBER() OVER(PARTITION BY loclevel ORDER BY id),*
FROM @T
)
SELECT id,loclevel,no FROM Liang AS A
WHERE EXISTS(
SELECT 1
FROM Liang
WHERE loclevel = A.loclevel
AND value_no = A.value_no
AND rowid BETWEEN A.rowid AND A.rowid + @n -1
GROUP BY loclevel
HAVING COUNT(*) >= @n
)/*
id loclevel no
----------- ----------- -----------
3 1 4
6 2 1
7 2 2(3 row(s) affected)*/
---------------------------------
-- Author: htl258(Tony)
-- Date : 2009-07-09 16:19:14
---------------------------------
--> 生成测试数据表:tbIf not object_id('[tb]') is null
Drop table [tb]
Go
Create table [tb]([id] int,[loclevel] int,[no] int)
Insert tb
Select 1,1,1 union all
Select 2,1,2 union all
Select 3,1,4 union all
Select 4,1,5 union all
Select 5,1,6 union all
Select 6,2,1 union all
Select 7,2,2 union all
Select 8,2,3 union all
Select 9,2,4 union all
Select 10,2,5 union all --再加两条记录
Select 11,2,6
Go
--Select * from tb-->SQL查询如下:
;with t as
(
select rn=row_number() over(order by loclevel,id)-no,*
from tb
)
select id,loclevel,no
from t a
where exists(
select 1
from t
where rn=a.rn
group by rn
having count(1)>=3)
and no not in(
select top 2 no
from t
where loclevel=a.loclevel
order by no desc)/*
id loclevel no
----------- ----------- -----------
3 1 4
6 2 1
7 2 2
8 2 3
9 2 4(5 行受影响)
*/
create table tt(id int,loclevel int,no int)
insert into tt select 1,1,1
insert into tt select 2,1,2
insert into tt select 3,1,4
insert into tt select 4,1,5
insert into tt select 5,1,6
insert into tt select 6,2,1
insert into tt select 7,2,2
insert into tt select 8,2,3
insert into tt select 9,2,4select t1.*
from tt t1 join tt t2
on t1.no = t2.no-1 and t1.loclevel = t2.loclevel
join tt t3 on t1.no = t3.no-2 and t1.loclevel = t3.loclevel/*
id loclevel no
3 1 4
6 2 1
7 2 2
*/
declare @t table(id int,loclevel int,no int)
insert into @t select 1,1,1
insert into @t select 2,1,2
insert into @t select 3,1,4
insert into @t select 4,1,2
insert into @t select 5,1,6
insert into @t select 6,2,5
insert into @t select 7,2,6
insert into @t select 8,2,7
insert into @t select 9,2,2
这样的数据 7 ,2 ,6查不出来
楼主的标题是:想在一个表中查询某个字段连续N条记录是连续+1的第一条记录......insert into @t select 7,2,6 --当此条记录作为第一条
insert into @t select 8,2,7 --连续+1
insert into @t select 9,2,2 --非连续+1明显不满足楼主的要求。难道是我理解错了?:)
insert into @t select 1,1,1
insert into @t select 2,1,2
insert into @t select 3,1,4
insert into @t select 4,1,2
insert into @t select 5,1,6
insert into @t select 6,2,5
insert into @t select 7,2,6
insert into @t select 8,2,7
insert into @t select 9,2,2 declare @i int
set @i=3 --当N值变化时,只需替换此处的@i值select
a.*
from
@t a,
(select t.* from @t t where not exists(select 1 from @t where id=t.id+1 and loclevel=t.loclevel and no=t.no+1)) b
where
a.id<=b.id and a.loclevel=b.loclevel
group by
a.id,a.loclevel,a.no
having
min(b.id)-a.id>=@i-1
/*
id loclevel no
----------- ----------- -----------
6 2 5
*/