ID num
1 4
20 4 --19
29 4 --9
38 4 --9
43 4 --5
55 4 --12
60 4 --5
84 4 --24
99 4 --15
111 4 --12
156 4 --45
159 4 --3
172 4 --13
173 4 --1
185 4 --12像这样的表结构,我想要的到得结果是
NUM 都是4得情况下,上下ID之间距离最高的那条结果显示就是(因数据量大,请各位高手注意效率)
4 45
1 4
20 4 --19
29 4 --9
38 4 --9
43 4 --5
55 4 --12
60 4 --5
84 4 --24
99 4 --15
111 4 --12
156 4 --45
159 4 --3
172 4 --13
173 4 --1
185 4 --12像这样的表结构,我想要的到得结果是
NUM 都是4得情况下,上下ID之间距离最高的那条结果显示就是(因数据量大,请各位高手注意效率)
4 45
a.num,max(b.id-a.id)
from
(select *,px=row_number()over(order by getdate()) from tb) a,
(select *,px=row_number()over(order by getdate()) from tb) b
where
a.px=b.px-1
group by
a.num
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-11-18 21:17:18
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[num] int)
insert [tb]
select 1,4 union all
select 20,4 union all
select 29,4 union all
select 38,4 union all
select 43,4 union all
select 55,4 union all
select 60,4 union all
select 84,4 union all
select 99,4 union all
select 111,4 union all
select 156,4 union all
select 159,4 union all
select 172,4 union all
select 173,4 union all
select 185,4
--------------开始查询--------------------------
select
a.num,max(b.id-a.id)
from
(select *,px=row_number()over(order by getdate()) from tb) a,
(select *,px=row_number()over(order by getdate()) from tb) b
where
a.px=b.px-1
group by
a.num
----------------结果----------------------------
/* num
----------- -----------
4 45(1 行受影响)*/
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[num] int)
Insert #T
select 1,4 union all
select 20,4 union all
select 29,4 union all
select 38,4 union all
select 43,4 union all
select 55,4 union all
select 60,4 union all
select 84,4 union all
select 99,4 union all
select 111,4 union all
select 156,4 union all
select 159,4 union all
select 172,4 union all
select 173,4 union all
select 185,4
Go
;with b
as
(Select *,ROW_NUMBER()over(partition by [num] order by ID) as row from #T)
select top 1 b.num,b.ID-a.ID as ID
from b as a
inner join b on a.row=b.row-1 and a.num=b.num
order by b.ID-a.ID desc
/*
num ID
4 45
*/
--> --> (Roy)生成測試數據
if not object_id('Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[num] int)
Insert #T
select 1,4 union all
select 20,4 union all
select 29,4 union all
select 38,4 union all
select 43,4 union all
select 55,4 union all
select 60,4 union all
select 84,4 union all
select 99,4 union all
select 111,4 union all
select 156,4 union all
select 159,4 union all
select 172,4 union all
select 173,4 union all
select 185,4
Go
select top 1
a.num,(b.ID-a.ID) as ID
from #T as a
left join #T as b on a.num=b.num and b.ID>a.ID and b.ID=(select min(ID) from #T where num=a.num and ID>a.ID)
order by b.ID-a.ID desc/*
num ID
4 45
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-11-18 21:17:18
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[num] int)
insert [tb]
select 1,4 union all
select 20,4 union all
select 29,4 union all
select 38,4 union all
select 43,4 union all
select 55,4 union all
select 60,4 union all
select 84,4 union all
select 99,4 union all
select 111,4 union all
select 156,4 union all
select 159,4 union all
select 172,4 union all
select 173,4 union all
select 185,4
--------------开始查询--------------------------
select
a.num,max(b.id-a.id)
from
(select *,px=row_number()over(order by getdate()) from tb where num=4) a,
(select *,px=row_number()over(order by getdate()) from tb where num=4) b
where
a.px=b.px-1
group by
a.num
----------------结果----------------------------
/* num
----------- -----------
4 45(1 行受影响)*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-11-18 21:17:18
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] int,[num] int)
insert [tb]
select 1,4 union all
select 20,4 union all
select 29,4 union all
select 38,4 union all
select 43,4 union all
select 55,4 union all
select 60,4 union all
select 84,4 union all
select 99,4 union all
select 111,4 union all
select 156,4 union all
select 159,4 union all
select 172,4 union all
select 173,4 union all
select 185,4
--------------开始查询--------------------------
select
a.num,max(b.id-a.id)
from
(select *,px=row_number()over(order by getdate()) from (select * from tb where num=4)t) a,
(select *,px=row_number()over(order by getdate()) from (select * from tb where num=4)t) b
where
a.px=b.px-1
group by
a.num----------------结果----------------------------
/* num
----------- -----------
4 45(1 行受影响)*/
select
top 1
a.num, ID=(select MIN(ID) from #T where num=a.num and ID>a.ID)-ID
from #T as a
order by ID desc/*
num ID
4 45
*/
消息 1904,级别 16,状态 1,第 1 行
表 't_allstar' 的 索引 'IX_t_allstar' 在 索引 键列表中具有 30 个列名。索引或统计信息键列列表的最大限制为 16。有没有办法解决呢