让缘随风 16:16:30
请教个问题
让缘随风 16:16:31
表DB:ID NUM1 800
2 855
3 866
4 800
5 844如何查NUM字段指定数据后一行记录?如NUM字段中800后一条记录
矢志不渝 16:18:52
==
请教个问题
让缘随风 16:16:31
表DB:ID NUM1 800
2 855
3 866
4 800
5 844如何查NUM字段指定数据后一行记录?如NUM字段中800后一条记录
矢志不渝 16:18:52
==
insert into tb values(1 , 800 )
insert into tb values(2 , 855 )
insert into tb values(3 , 866 )
insert into tb values(4 , 800 )
insert into tb values(5 , 844 )
goselect m.* from tb m , tb n where m.id = n.id + 1 and n.num = 800drop table tb/*ID NUM
----------- -----------
2 855
5 844(所影响的行数为 2 行)
*/
insert into tb values(1 , 800 )
insert into tb values(2 , 855 )
insert into tb values(3 , 866 )
insert into tb values(4 , 800 )
insert into tb values(5 , 844 )
go--如果ID连续
select m.* from tb m , tb n where m.id = n.id + 1 and n.num = 800
/*ID NUM
----------- -----------
2 855
5 844(所影响的行数为 2 行)
*/--如果ID不连续,sql 2000
select m.id , m.num from
(
select t.* , px = (select count(1) from tb where id < t.id) + 1 from tb t
) m,
(
select t.* , px = (select count(1) from tb where id < t.id) + 1 from tb t
) n
where m.id = n.id + 1 and n.num = 800
/*ID NUM
----------- -----------
2 855
5 844(所影响的行数为 2 行)
*/--如果ID不连续,sql 2005select m.id , m.num from
(
select t.* , px = row_number() over(order by id) from tb t
) m,
(
select t.* , px = row_number() over(order by id) from tb t
) n
where m.id = n.id + 1 and n.num = 800
/*ID NUM
----------- -----------
2 855
5 844(所影响的行数为 2 行)
*/drop table tb
where b.num=800 and a.id1=b.id1+1
create table tb(ID int, NUM int)
insert into tb values(1 , 800 )
insert into tb values(2 , 855 )
insert into tb values(3 , 866 )
insert into tb values(4 , 800 )
insert into tb values(5 , 844 )
goselect *
from tb
where id in (select id+1 from tb where num = 800)drop table tb/*
ID NUM
----------- -----------
2 855
5 844
select top (1) * from t where num>800
select * from tb where id=(select id+1 from tb where num=800)
select * from tb where id in (select id+1 from tb where num=800)
create table tb(ID int, NUM int)
insert into tb values(1 , 800 )
insert into tb values(2 , 855 )
insert into tb values(3 , 866 )
insert into tb values(4 , 800 )
insert into tb values(5 , 844 )
go
with cte as
(select rn=ROW_NUMBER()over(order by getdate()),* from tb )
select a.*
from cte a join cte b on a.rn=b.rn+1
where b.NUM=800
/*
rn ID NUM
-------------------- ----------- -----------
2 2 855
5 5 844*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-18 19:21:44
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (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,800 union all
select 2,855 union all
select 3,866 union all
select 4,800 union all
select 5,844
--------------开始查询--------------------------
select
a.id , b.num
from
(select * , id0 = row_number() over(order by id) from tb t) a,
(select * , id0 = row_number() over(order by id) from tb t) b
where a.id0 = b.id0 + 1 and b.num = 800----------------结果----------------------------
/* id num
----------- -----------
2 800
5 800(2 行受影响)*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-18 19:21:44
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (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,800 union all
select 2,855 union all
select 3,866 union all
select 4,800 union all
select 5,844
--------------开始查询--------------------------
select
a.id , a.num
from
(select * , id0 = row_number() over(order by getdate()) from tb t) a,
(select * , id0 = row_number() over(order by getdate()) from tb t) b
where a.id0 = b.id0 + 1 and b.num = 800----------------结果----------------------------
/*id num
----------- -----------
2 855
5 844(2 行受影响)
*/