数据类型:
Code char(6)
CreateTime datetime
Price float 数据如下:Code CreateTime Price
031002 2008-10-17 15:00:15 3.58
031012 2008-10-17 15:00:16 5.53
031022 2008-10-17 15:00:17 4.54
031032 2008-10-17 15:00:18 3.51
031013 2008-10-17 15:00:19 3.52
031023 2008-10-17 15:00:20 2.50
031033 2008-10-17 15:00:21 4.53
031054 2008-10-17 15:00:22 3.55
031004 2008-10-17 15:00:23 3.55
031024 2008-10-17 15:00:24 5.57我查询
SELECT * FROM Table Where Price between 4 and 5
出来的结果是031022 2008-10-17 15:00:17 4.54
031033 2008-10-17 15:00:21 4.53我想要的结果是031032 2008-10-17 15:00:18 3.51
031054 2008-10-17 15:00:22 3.55就是说要符合查询的条件的下一条,他本条跳过,请问这句SQL要怎么写,谢谢!
Code char(6)
CreateTime datetime
Price float 数据如下:Code CreateTime Price
031002 2008-10-17 15:00:15 3.58
031012 2008-10-17 15:00:16 5.53
031022 2008-10-17 15:00:17 4.54
031032 2008-10-17 15:00:18 3.51
031013 2008-10-17 15:00:19 3.52
031023 2008-10-17 15:00:20 2.50
031033 2008-10-17 15:00:21 4.53
031054 2008-10-17 15:00:22 3.55
031004 2008-10-17 15:00:23 3.55
031024 2008-10-17 15:00:24 5.57我查询
SELECT * FROM Table Where Price between 4 and 5
出来的结果是031022 2008-10-17 15:00:17 4.54
031033 2008-10-17 15:00:21 4.53我想要的结果是031032 2008-10-17 15:00:18 3.51
031054 2008-10-17 15:00:22 3.55就是说要符合查询的条件的下一条,他本条跳过,请问这句SQL要怎么写,谢谢!
exists (SELECT 1 FROM #temp Where Price between 4 and 5 and t.id-1=id)未测试
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-10-12 21:49:37
-- 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]([Code] varchar(6),[CreateTime] datetime,[Price] numeric(3,2))
insert [tb]
select '031002','2008-10-17 15:00:15',3.58 union all
select '031012','2008-10-17 15:00:16',5.53 union all
select '031022','2008-10-17 15:00:17',4.54 union all
select '031032','2008-10-17 15:00:18',3.51 union all
select '031013','2008-10-17 15:00:19',3.52 union all
select '031023','2008-10-17 15:00:20',2.50 union all
select '031033','2008-10-17 15:00:21',4.53 union all
select '031054','2008-10-17 15:00:22',3.55 union all
select '031004','2008-10-17 15:00:23',3.55 union all
select '031024','2008-10-17 15:00:24',5.57
--------------开始查询--------------------------
select *,id=identity(int) into #t from tb
select Code,CreateTime,Price from #t where id in (select id+1 from #t where Price between 4 and 5)
drop table #t
----------------结果----------------------------
/* Code CreateTime Price
------ ----------------------- ---------------------------------------
031032 2008-10-17 15:00:18.000 3.51
031054 2008-10-17 15:00:22.000 3.55(2 行受影响)*/
=====》
SELECT *,(select count(1) from [table] where CreateTime<=T.CreateTime) as id into #temp FROM [Table] t Where Price between 4 and 5
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Code] varchar(6),[CreateTime] datetime,[Price] numeric(3,2))
insert [tb]
select '031002','2008-10-17 15:00:15',3.58 union all
select '031012','2008-10-17 15:00:16',5.53 union all
select '031022','2008-10-17 15:00:17',4.54 union all
select '031032','2008-10-17 15:00:18',3.51 union all
select '031013','2008-10-17 15:00:19',3.52 union all
select '031023','2008-10-17 15:00:20',2.50 union all
select '031033','2008-10-17 15:00:21',4.53 union all
select '031054','2008-10-17 15:00:22',3.55 union all
select '031004','2008-10-17 15:00:23',3.55 union all
select '031024','2008-10-17 15:00:24',5.57alter table tb add id int identity(1,1)select Code , CreateTime , Price from tb where id in
(
select id+1 from tb Where Price between 4 and 5
) alter table tb drop column id /*
Code CreateTime Price
------ ----------------------- ---------------------------------------
031032 2008-10-17 15:00:18.000 3.51
031054 2008-10-17 15:00:22.000 3.55(2 行受影响)
*/