数据类型:
Code char(6)
CreateTime datetime
Price float 数据如下: Code CreateTime Price
031021 2008-10-17 15:00:15 3.58
031021 2008-10-17 15:00:16 5.53
031021 2008-10-17 15:00:17 4.54
031021 2008-10-17 15:00:18 3.51
031021 2008-10-17 15:00:19 3.52
031024 2008-10-17 15:00:20 2.50
031024 2008-10-17 15:00:21 4.53
031024 2008-10-17 15:00:22 3.55
031024 2008-10-17 15:00:23 3.55
031024 2008-10-17 15:00:24 5.57我想输入两个Price开始范围和结束范围
开始条件 Price between 4 and 5
结束条件 Price <4我想要的查询结果就是 031021 2008-10-17 15:00:15 3.58
031021 2008-10-17 15:00:16 5.53
031021 2008-10-17 15:00:17 4.54
031021 2008-10-17 15:00:18 3.51
031024 2008-10-17 15:00:21 4.53
031024 2008-10-17 15:00:22 3.55以每个Code为单位,符合开始条件就要,直到不符合结束条件就停止,包括不符合的那条。
请问这句SQL要怎么写,谢谢!
Code char(6)
CreateTime datetime
Price float 数据如下: Code CreateTime Price
031021 2008-10-17 15:00:15 3.58
031021 2008-10-17 15:00:16 5.53
031021 2008-10-17 15:00:17 4.54
031021 2008-10-17 15:00:18 3.51
031021 2008-10-17 15:00:19 3.52
031024 2008-10-17 15:00:20 2.50
031024 2008-10-17 15:00:21 4.53
031024 2008-10-17 15:00:22 3.55
031024 2008-10-17 15:00:23 3.55
031024 2008-10-17 15:00:24 5.57我想输入两个Price开始范围和结束范围
开始条件 Price between 4 and 5
结束条件 Price <4我想要的查询结果就是 031021 2008-10-17 15:00:15 3.58
031021 2008-10-17 15:00:16 5.53
031021 2008-10-17 15:00:17 4.54
031021 2008-10-17 15:00:18 3.51
031024 2008-10-17 15:00:21 4.53
031024 2008-10-17 15:00:22 3.55以每个Code为单位,符合开始条件就要,直到不符合结束条件就停止,包括不符合的那条。
请问这句SQL要怎么写,谢谢!
开始条件 Price between 4 and 5
结束条件 Price <4 这是什么条件??
开始值是:开始条件 Price between 4 and 5 那结果
031021 2008-10-17 15:00:15 3.58
031021 2008-10-17 15:00:16 5.53
这两条应该不会是开头啊,也就是说不满足条件了
怎么解释?
031021 2008-10-17 15:00:15 3.58
031021 2008-10-17 15:00:16 5.53
031021 2008-10-17 15:00:17 4.54
031021 2008-10-17 15:00:18 3.51
031021 2008-10-17 15:00:19 3.52
031024 2008-10-17 15:00:20 2.50
031024 2008-10-17 15:00:21 4.53
031024 2008-10-17 15:00:22 3.55
031024 2008-10-17 15:00:23 3.55
031024 2008-10-17 15:00:24 5.57
这是数据,我想查询后的结果为
031021 2008-10-17 15:00:15 3.58
031021 2008-10-17 15:00:16 5.53
031021 2008-10-17 15:00:17 4.54
031021 2008-10-17 15:00:18 3.51
031024 2008-10-17 15:00:21 4.53
031024 2008-10-17 15:00:22 3.55这个数据是这样得到的
输入Price开始范围和结束范围
开始条件 Price between 4 and 5
结束条件 Price <4
这个开始与结束就是指每个Code从上往下走,只要符合Price between 4 and 5 就开始,走到Price <4就停止
结束条件 Price <4
那这个又是怎么回事
031021 2008-10-17 15:00:16 5.53
开始条件 Price between 4 and 5
结束条件 Price <4
是不是先搜索Price between 4 and 5
再搜索Price <4 ?
两次搜索是AND还是OR,AND话岂不是矛盾?OR的话,那何不直接Price <5?
你觉得你给的查询结果,与你下的条件相符合吗?
031021 2008-10-17 15:00:15 3.58
031021 2008-10-17 15:00:16 5.53
031021 2008-10-17 15:00:17 4.54
031021 2008-10-17 15:00:18 3.51
031021 2008-10-17 15:00:19 3.52
031024 2008-10-17 15:00:20 2.50
031024 2008-10-17 15:00:21 4.53
031024 2008-10-17 15:00:22 3.55
031024 2008-10-17 15:00:23 3.55
031024 2008-10-17 15:00:24 5.57
这是数据,我想查询后的结果为
031021 2008-10-17 15:00:15 3.58
031021 2008-10-17 15:00:16 5.53
031021 2008-10-17 15:00:17 4.54
031021 2008-10-17 15:00:18 3.51
031024 2008-10-17 15:00:21 4.53
031024 2008-10-17 15:00:22 3.55 这个数据是这样得到的
输入Price开始范围和结束范围
开始条件 Price between 4 and 5
结束条件 Price <4
这个开始与结束就是指每个Code从上往下走,只要符合Price between 4 and 5 就开始,走到Price <4就停止这就是给的两个条件,具体怎么写我不会,需要请教大家。
031021 2008-10-17 15:00:16 5.53
这符合吗?在4,5之间吗?
price=2.50 < 4 为什么不在里面呢?呵呵.............
select Code, Createtime, Price from tb
where price <= (select min(price) from tb where price >5 );
select Code, Createtime, Price from tb
where price >= (select min(price) from tb where price >3 )
and price <= (select min(price) from tb where price >5 )
031021 2008-10-17 15:00:15 4.58
可以用下面的--> Test data : @t
declare @t table ([Code] varchar(6),[CreateTime] datetime,[Price] numeric(3,2))
insert into @t
select '031021','2008-10-17 15:00:15',4.58 union all
select '031021','2008-10-17 15:00:16',5.53 union all
select '031021','2008-10-17 15:00:17',4.54 union all
select '031021','2008-10-17 15:00:18',3.51 union all
select '031021','2008-10-17 15:00:19',3.52 union all
select '031024','2008-10-17 15:00:20',2.50 union all
select '031024','2008-10-17 15:00:21',4.53 union all
select '031024','2008-10-17 15:00:22',3.55 union all
select '031024','2008-10-17 15:00:23',3.55 union all
select '031024','2008-10-17 15:00:24',5.57;with t1 as
(select code,min(createtime) startTime
from @t
where price between 4 and 5
group by code
), t2 as
(select t.code,min(t.createtime)as endTime
from @t t,t1
where t.code = t1.code
and t.createtime > t1.startTime
and t.price < 4
group by t.code
)
select t.*
from @t as t, t1,t2
where t.code = t1.code
and t.code = t2.code
and t.createtime >= t1.startTime
and t.createtime <= t2.endTime
Code CreateTime Price
------ ----------------------- ---------------------------------------
031021 2008-10-17 15:00:15.000 4.58
031021 2008-10-17 15:00:16.000 5.53
031021 2008-10-17 15:00:17.000 4.54
031021 2008-10-17 15:00:18.000 3.51
031024 2008-10-17 15:00:21.000 4.53
031024 2008-10-17 15:00:22.000 3.55
declare @t table ([Code] varchar(6),[CreateTime] datetime,[Price] numeric(3,2))
insert into @t
select '031021','2008-10-17 15:00:15',4.58 union all
select '031021','2008-10-17 15:00:16',5.53 union all
select '031021','2008-10-17 15:00:17',4.54 union all
select '031021','2008-10-17 15:00:18',3.51 union all
select '031021','2008-10-17 15:00:19',3.52 union all
select '031024','2008-10-17 15:00:20',2.50 union all
select '031024','2008-10-17 15:00:21',4.53 union all
select '031024','2008-10-17 15:00:22',3.55 union all
select '031024','2008-10-17 15:00:23',3.55 union all
select '031024','2008-10-17 15:00:24',5.57;with t1 as
(select code,min(createtime) startTime
from @t
where price between 4 and 5
group by code
), t2 as
(select t1.code,min(t.createtime)as endTime
from t1 left join @t t
on t1.code = t.code
and t1.startTime <= t.createtime
and t.price < 4
group by t1.code
)select t.*
from @t as t, t1,t2
where t.code = t1.code
and t.createtime >= t1.startTime
and t.code = t2.code
and (
(t.createtime <= t2.endTime )
or
(t2.endTime is null)
)
Code CreateTime Price
------ ----------------------- ---------------------------------------
031021 2008-10-17 15:00:15.000 4.58
031021 2008-10-17 15:00:16.000 5.53
031021 2008-10-17 15:00:17.000 4.54
031021 2008-10-17 15:00:18.000 3.51
031024 2008-10-17 15:00:21.000 4.53
031024 2008-10-17 15:00:22.000 3.55
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 '031021','2008-10-17 15:00:15',4.58 union all
select '031021','2008-10-17 15:00:16',5.53 union all
select '031021','2008-10-17 15:00:17',4.54 union all
select '031021','2008-10-17 15:00:18',3.51 union all
select '031021','2008-10-17 15:00:19',3.52 union all
select '031024','2008-10-17 15:00:20',2.50 union all
select '031024','2008-10-17 15:00:21',4.53 union all
select '031024','2008-10-17 15:00:22',3.55 union all
select '031024','2008-10-17 15:00:23',3.55 union all
select '031024','2008-10-17 15:00:24',5.57
alter table tb add id int identity(1,1)
select tb.code,tb.createtime,tb.price from tb,
(
select code,
(select min(id) from tb where code=t.code and price between 4 and 5) as spoint,
(select min(id) from tb where code=t.code and price<4
and id>(select min(id) from tb where code=t.code and price between 4 and 5)) as epoint
from tb t
group by code) m
where tb.code=m.code and id between spoint and epointalter table tb drop column id /*
code createtime price
------ ----------------------- ---------------------------------------
031021 2008-10-17 15:00:15.000 4.58
031021 2008-10-17 15:00:16.000 5.53
031021 2008-10-17 15:00:17.000 4.54
031021 2008-10-17 15:00:18.000 3.51
031024 2008-10-17 15:00:21.000 4.53
031024 2008-10-17 15:00:22.000 3.55
*/