数据如下: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
我想查询后的结果为
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就停止大家可能会觉得这样的条件有些矛盾不符合常理,其实是没有矛盾的,只是有些麻烦,我实在搞不定了,请大家帮忙看看,谢谢!
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
我想查询后的结果为
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就停止大家可能会觉得这样的条件有些矛盾不符合常理,其实是没有矛盾的,只是有些麻烦,我实在搞不定了,请大家帮忙看看,谢谢!
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.57select * from
(
select * from tb where price between 4 and 5
union all
select a.code, a.createtime, a.price from tb a join
(
select code, min(createtime) as createtime from tb where price < 4 group by code
) b on a.code = b.code and a.createtime = b.createtime
) t order by code, createtime
/*
Code CreateTime Price
------ ------------------------------------------------------ -----
031021 2008-10-17 15:00:15.000 4.58
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:20.000 2.50
031024 2008-10-17 15:00:21.000 4.53
*/
--楼主是这个意思吗?只要一条记录在,4 and 5 就开始,并且取这条记录的前一条记录,
--直到<4,结束!--是这个意思吗?
select * from
(
select * from tb where price between 4 and 5
union all
select a.code, a.createtime, a.price from tb a join
(
select code, min(createtime) as createtime from tb where price < 4 group by code
) b on a.code = b.code and a.createtime = b.createtime
) t order by code, createtime
这句能查出来,但是效率好像不行。有点慢
declare @time datetime
declare @price numeric(3,2)
declare @bo int
declare @tb table([Code] varchar(6),[CreateTime] datetime,[Price] numeric(3,2))
declare Cu cursor for select CreateTime,Price from tb
open cu
fetch next from cu into @time,@price
set @bo=1
while @@fetch_status=0
begin
if @price between 4 and 5
begin
set @bo=0
end
if @bo=0
begin
insert @tb select * from tb where CreateTime=@time
end
if @price<4
begin
set @bo=1
end
fetch next from cu into @time,@price
end
close cu
deallocate cu
select * from @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.57select tb.* from tb join
(
select a.code, b.createtime as mintime, min(a.createtime) as maxtime from tb a join
(
select code, min(createtime) as createtime from tb where price >= 4 group by code
) b on a.code = b.code and a.createtime > b.createtime and a.price < 4
group by a.code, b.createtime
) t on tb.code = t.code and tb.createtime >= t.mintime and tb.createtime <= t.maxtime
/*
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
*/
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
select tb.code,tb.createtime,tb.price from tb,
(
select code,
(select min([CreateTime]) from tb where code=t.code and price between 4 and 5) as spoint,
(select min([CreateTime]) from tb where code=t.code and price<4
and [CreateTime]>(select min([CreateTime]) 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 [CreateTime] between spoint and epoint
/*
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
*/
open c_tbdeclare @code varchar(6), @CreateTime datetime,@price numeric(3,2), @inc int
declare @temp table([Code] varchar(6),[CreateTime] datetime,[Price] numeric(3,2))
set @inc = 0
fetch next from c_tb into @code,@CreateTime,@price
while @@FETCH_STATUS = 0
begin
if @inc = 0 and @price >= 4 and @price <= 5
begin
insert into @temp select @code,@CreateTime,@price
set @inc = @inc + 1
end
else if @inc > 0 and @price > 4
begin
insert into @temp select @code,@CreateTime,@price
set @inc = @inc + 1
end
else if @inc > 0 and @price < 4
begin
insert into @temp select @code,@CreateTime,@price
set @inc = 0
end
fetch next from c_tb into @code,@CreateTime,@price
endclose c_tb
deallocate c_tbselect * from @temp
select distinct code
,(select top 1 taoistong from #taoistong b where a.code=b.code and price between 4 and 5 order by taoistong) t_taoistong
,(select top 1 price from #taoistong b where a.code=b.code and price between 4 and 5 order by taoistong) t_price
into #taoistong_t
from #taoistong aselect distinct code
,(select top 1 taoistong from #taoistong b where a.code=b.code and price<4
and taoistong>((select top 1 taoistong from #taoistong b where a.code=b.code and price between 4 and 5 order by taoistong))
order by taoistong) e_taoistong
,(select top 1 price from #taoistong b where a.code=b.code and price <4
and taoistong>((select top 1 taoistong from #taoistong b where a.code=b.code and price between 4 and 5 order by taoistong))
order by taoistong) e_price
into #taoistong_e
from #taoistong aselect a. *
from (select a.*
from #taoistong a,#taoistong_t b
where a.code=b.code
and a.taoistong>=b.t_taoistong) a,#taoistong_e c
where a.code=c.code
and a.taoistong<=c.e_taoistongdrop table #taoistong_t
drop table #taoistong_e
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.57select identity(int) as id, * into # from tbselect #.* from # join
(
select a.code, b.id as minid, min(a.id) as maxid from # a join
(
select code, min(id) as id from # where price >= 4 and price <= 5 group by code
) b on a.code = b.code and a.id > b.id and a.price < 4
group by a.code, b.id
) t on #.code = t.code and #.id >= t.minid and #.id <= t.maxid
/*
id Code CreateTime Price
----------- ------ ------------------------------------------------------ -----
1 031021 2008-10-17 15:00:15.000 4.58
2 031021 2008-10-17 15:00:16.000 5.53
3 031021 2008-10-17 15:00:17.000 4.54
4 031021 2008-10-17 15:00:18.000 3.51
7 031024 2008-10-17 15:00:21.000 4.53
8 031024 2008-10-17 15:00:22.000 3.55
*/
原来要分组看的
group by code来观察,每一组都有楼组说的规律了