if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] [int] IDENTITY(1,1),[Code] varchar(6),[CreateTime] datetime,[Price] numeric(3,2))
insert [tb]
select '031021','2008-10-17 15:00:00',4.58 union all
select '031021','2008-10-18 15:00:00',5.53 union all
select '031021','2008-10-19 15:00:00',4.54 union all
select '031021','2008-10-20 15:00:00',3.51 union all
select '031021','2008-10-21 15:00:00',3.52 union all
select '031021','2008-10-22 15:00:00',3.63 union all
select '031024','2008-10-16 15:00:00',2.51 union all
select '031024','2008-10-17 15:00:00',2.50 union all
select '031024','2008-10-18 15:00:00',4.53 union all
select '031024','2008-10-19 15:00:00',3.55 union all
select '031024','2008-10-20 15:00:00',3.55 union all
select '031024','2008-10-21 15:00:00',5.57 union all
select '031025','2008-10-17 15:00:00',2.50 union all
select '031025','2008-10-18 15:00:00',4.53 union all
select '031025','2008-10-19 15:00:00',5.55 union all
select '031025','2008-10-20 15:00:00',5.55 union all
select '031025','2008-10-21 15:00:00',5.57
我想查询后的结果为
031021 2008-10-17 15:00:00 4.58
031021 2008-10-18 15:00:00 5.53
031021 2008-10-19 15:00:00 4.54
031021 2008-10-20 15:00:00 3.51
031024 2008-10-18 15:00:00 4.53
031024 2008-10-19 15:00:00 3.55
031025 2008-10-18 15:00:00 4.53
031025 2008-10-19 15:00:00 5.55
031025 2008-10-20 15:00:00 5.55
031025 2008-10-21 15:00:00 5.57 这个数据是这样得到的
输入Price开始范围和结束范围
开始条件 Price between 4 and 5
结束条件 Price <4
这个开始与结束就是指每个Code从上往下走,只要符合Price between 4 and 5 就开始,走到Price <4就停止
(注意:031025 因为结束条件没有符合Price <4 所以从开始以后的就全部列出来)
没有符合结束条件从开始以后的就全部列出来这种情况一定要考虑到。 大家可能会觉得这样的条件有些矛盾不符合常理,其实是没有矛盾的,只是有些麻烦,我实在搞不定了,请大家帮忙看看。
之前开过贴了,可是有几位大哥给我的语句查询会超时,我现在库里有100多万条数据。能不能给我个效率高的?谢谢!
go
create table [tb]([id] [int] IDENTITY(1,1),[Code] varchar(6),[CreateTime] datetime,[Price] numeric(3,2))
insert [tb]
select '031021','2008-10-17 15:00:00',4.58 union all
select '031021','2008-10-18 15:00:00',5.53 union all
select '031021','2008-10-19 15:00:00',4.54 union all
select '031021','2008-10-20 15:00:00',3.51 union all
select '031021','2008-10-21 15:00:00',3.52 union all
select '031021','2008-10-22 15:00:00',3.63 union all
select '031024','2008-10-16 15:00:00',2.51 union all
select '031024','2008-10-17 15:00:00',2.50 union all
select '031024','2008-10-18 15:00:00',4.53 union all
select '031024','2008-10-19 15:00:00',3.55 union all
select '031024','2008-10-20 15:00:00',3.55 union all
select '031024','2008-10-21 15:00:00',5.57 union all
select '031025','2008-10-17 15:00:00',2.50 union all
select '031025','2008-10-18 15:00:00',4.53 union all
select '031025','2008-10-19 15:00:00',5.55 union all
select '031025','2008-10-20 15:00:00',5.55 union all
select '031025','2008-10-21 15:00:00',5.57
我想查询后的结果为
031021 2008-10-17 15:00:00 4.58
031021 2008-10-18 15:00:00 5.53
031021 2008-10-19 15:00:00 4.54
031021 2008-10-20 15:00:00 3.51
031024 2008-10-18 15:00:00 4.53
031024 2008-10-19 15:00:00 3.55
031025 2008-10-18 15:00:00 4.53
031025 2008-10-19 15:00:00 5.55
031025 2008-10-20 15:00:00 5.55
031025 2008-10-21 15:00:00 5.57 这个数据是这样得到的
输入Price开始范围和结束范围
开始条件 Price between 4 and 5
结束条件 Price <4
这个开始与结束就是指每个Code从上往下走,只要符合Price between 4 and 5 就开始,走到Price <4就停止
(注意:031025 因为结束条件没有符合Price <4 所以从开始以后的就全部列出来)
没有符合结束条件从开始以后的就全部列出来这种情况一定要考虑到。 大家可能会觉得这样的条件有些矛盾不符合常理,其实是没有矛盾的,只是有些麻烦,我实在搞不定了,请大家帮忙看看。
之前开过贴了,可是有几位大哥给我的语句查询会超时,我现在库里有100多万条数据。能不能给我个效率高的?谢谢!
as
declare @called varchar(500)
declare @code varchar(500)
DECLARE bankjgdm CURSOR FOR
SELECT distinct(code)
FROM tb
OPEN bankjgdmFETCH NEXT FROM bankjgdm into @called
WHILE @@FETCH_STATUS = 0
BEGIN
set @code=(select top 1 id from tb where code =@called and Price <'4' and id >(select top 1 id from tb where code =@called and Price between 4 and 6 ))
if @code is null
begin
select * from tb where id >=(select top 1 id from tb where code =@called and Price between 4 and 6 ) and code =@called
end
else
begin
select * from tb where id >=(select a.id from (select top 1 * from tb where code =@called and Price between 4 and 6 ) a
join
(select top 1 * from tb where code =@called and Price <'4' and id >(select top 1 id from tb where code =@called and Price between 4 and 6 )) b
on a.code = b.code) and id <=(select b.id from (select top 1 * from tb where code =@called and Price between 4 and 6 ) a
join
(select top 1 * from tb where code =@called and Price <'4' and id >(select top 1 id from tb where code =@called and Price between 4 and 6 )) b
on a.code = b.code)
endFETCH NEXT FROM bankjgdm into @calledENDCLOSE bankjgdm
DEALLOCATE bankjgdm
GO
查询出来的结果是分开窗体显示。
你那个很慢,在查询分析器上运行了几分钟都还没结果。
js_szy 的要在查询分析器上运行1分多钟才能查出来。
select tb.* from(
select Code,MIN(CreateTime)as CreateTime,isnull((select top 1 CreateTime from tb as T where Code=tb.Code and CreateTime>MIN(tb.CreateTime) and Price<4),'2010-1-1')as endTime from tb where Price between 4 and 5 group by Code
)as a left join tb on a.Code=tb.Code where tb.CreateTime between a.CreateTime and a.endTimeselect tb.* from(
select Code,MIN(CreateTime)as CreateTime from tb where Price between 4 and 5 group by Code
)as a left join tb on a.Code=tb.Code where tb.CreateTime between a.CreateTime and isnull((select top 1 CreateTime from tb as T where Code=a.Code and CreateTime>a.CreateTime and Price<4),'2010-1-1')select tb.* from(
select *,isnull((select top 1 CreateTime from tb as T where Code=a.Code and CreateTime>a.CreateTime and Price<4),'2010-1-1')as endTime from(
select Code,MIN(CreateTime)as CreateTime from tb where Price between 4 and 5 group by Code
)as a
)as b left join tb on b.Code=tb.Code where tb.CreateTime between b.CreateTime and b.endTime
直接用sql语句时,记录多时会很慢,因为检索每一条记录时,都要对前面的记录进行逐条比较。
有序的东西不是sql的专长。
建议用根据时间条件建游标,逐条数可能会更快。
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] [int] IDENTITY(1,1),[Code] varchar(6),[CreateTime] datetime,[Price] numeric(3,2))
insert [tb]
select '031021','2008-10-17 15:00:00',4.58 union all
select '031021','2008-10-18 15:00:00',5.53 union all
select '031021','2008-10-19 15:00:00',4.54 union all
select '031021','2008-10-20 15:00:00',3.51 union all
select '031021','2008-10-21 15:00:00',3.52 union all
select '031021','2008-10-22 15:00:00',3.63 union all
select '031024','2008-10-16 15:00:00',2.51 union all
select '031024','2008-10-17 15:00:00',2.50 union all
select '031024','2008-10-18 15:00:00',4.53 union all
select '031024','2008-10-19 15:00:00',3.55 union all
select '031024','2008-10-20 15:00:00',3.55 union all
select '031024','2008-10-21 15:00:00',5.57 union all
select '031025','2008-10-17 15:00:00',2.50 union all
select '031025','2008-10-18 15:00:00',4.53 union all
select '031025','2008-10-19 15:00:00',5.55 union all
select '031025','2008-10-20 15:00:00',5.55 union all
select '031025','2008-10-21 15:00:00',5.57go--假设记录是按id顺序排列,如按其它方法排序,根据相同道理,更改查询条件。
declare @sqltext varchar(8000),@sch varchar(200),@id int,@maxid int
select @maxid=max(id) from tb
set @id=0
set @sqltext=''
while @id<@maxid
begin
set @id=(select min(id) from tb where id>@id and price between 4 and 5)
if @id is null break set @sch='id between '+convert(varchar(30),@id)
set @id=(select min(id) from tb where id>@id and price<4)
if @id is null set @id=@maxid
set @sch=@sch+' and '+convert(varchar(30),@id)
if @sqltext=''
set @sqltext=@sch
else
set @sqltext=@sqltext+' or '+@sch
end
if @sqltext<>''
set @sqltext='select * from tb where '+@sqltext
exec(@sqltext)如果太长,可将条件存于临时表中,再用临时表与表交叉连接查询,不过速度可能会慢一些.
select tb.* from(
select Code,MIN(CreateTime)as CreateTime,isnull((select top 1 CreateTime from tb as T where Code=tb.Code and CreateTime>MIN(tb.CreateTime) and Price<4 order by CreateTime),'2010-1-1')as endTime from tb where Price between 4 and 5 group by Code
)as a left join tb on a.Code=tb.Code where tb.CreateTime between a.CreateTime and a.endTime
DECLARE @TB TABLE ([id] int,[Code] varchar(6),[CreateTime] datetime,[Price] numeric(3,2))
DECLARE @Price2 numeric(3,2);
DECLARE @Id INT, @Code VARCHAR(6), @Createtime DATETIME, @Price numeric(3,2), @flag1 INT;
SET @flag1=0;DECLARE @MyData CURSOR
SET @MyData = CURSOR FOR
SELECT Id, Code, Createtime, Price from tb;
OPEN @MyData
FETCH NEXT FROM @MyData INTO @Id, @Code, @Createtime, @Price
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@flag1<>0 AND @Price>=4.0 AND @Price<5.0)
BEGIN
SET @flag1=0;
END
IF(@flag1=0 AND @Price>=4.0 AND @Price<6.0)
BEGIN
INSERT INTO @TB VALUES(@Id, @Code, @Createtime, @Price)
--SET @flag2=1;
END
IF(@flag1=0 AND @Price<4.0)
BEGIN
INSERT INTO @TB VALUES(@Id, @Code, @Createtime, @Price)
SET @flag1=1;
-- SET @flag2=1;
END FETCH NEXT FROM @MyData INTO @Id, @Code, @Createtime, @Price
END
CLOSE @MyData
DEALLOCATE @MyData SELECT DISTINCT * FROM @TB;
DECLARE @TB TABLE ([id] int,[Code] varchar(6),[CreateTime] datetime,[Price] numeric(3,2))
DECLARE @Price2 numeric(3,2);
DECLARE @Id INT, @Code VARCHAR(6), @Createtime DATETIME, @Price numeric(3,2), @flag1 INT;
SET @flag1=0;DECLARE @MyData CURSOR
SET @MyData = CURSOR FOR
SELECT Id, Code, Createtime, Price from tb;
OPEN @MyData
FETCH NEXT FROM @MyData INTO @Id, @Code, @Createtime, @Price
WHILE @@FETCH_STATUS = 0
BEGIN
IF(@flag1<>0 AND @Price>=4.0 AND @Price<5.0)
BEGIN
SET @flag1=0;
END
IF(@flag1=0 AND @Price>=4.0)
BEGIN
INSERT INTO @TB VALUES(@Id, @Code, @Createtime, @Price)
END
IF(@flag1=0 AND @Price<4.0)
BEGIN
INSERT INTO @TB VALUES(@Id, @Code, @Createtime, @Price)
SET @flag1=1;
END FETCH NEXT FROM @MyData INTO @Id, @Code, @Createtime, @Price
END
CLOSE @MyData
DEALLOCATE @MyData SELECT DISTINCT * FROM @TB;