数据如下: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 '031024','2008-10-10 15:00:00',4.58 union all
select '031024','2008-10-11 15:00:00',5.53 union all
select '031024','2008-10-12 15:00:00',4.54 union all
select '031024','2008-10-13 15:00:00',3.51 union all
select '031024','2008-10-14 15:00:00',3.52 union all
select '031024','2008-10-15 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 union all
select '031025','2008-10-22 15:00:00',2.50 union all
select '031025','2008-10-23 15:00:00',4.53 union all
select '031025','2008-10-24 15:00:00',5.55 union all
select '031025','2008-10-25 15:00:00',5.55 union all
select '031025','2008-10-26 15:00:00',5.57
我想查询后的结果为
031024 2008-10-10 15:00:00 4.58
031024 2008-10-11 15:00:00 5.53
031024 2008-10-12 15:00:00 4.54
031024 2008-10-13 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
031025 2008-10-22 15:00:00 2.50
031025 2008-10-23 15:00:00 4.53
031025 2008-10-24 15:00:00 5.55
031025 2008-10-25 15:00:00 5.55
031025 2008-10-26 15:00:00 5.57这个数据是这样得到的
输入Price开始范围和结束范围
开始条件 Price between 4 and 5
结束条件 Price <4
这个开始与结束就是指每个Code从上往下走,只要符合Price between 4 and 5 就开始,走到Price <4就停止,停止后继续往下走,只要符合Price between 4 and 5 就开始,走到Price <4就停止,直到没有数据为止。
(注意:031025 因为结束条件没有符合Price <4 所以最后的数据就全部列出来)
没有符合结束条件从开始以后的就全部列出来这种情况一定要考虑到。
大家可能会觉得这样的条件有些矛盾不符合常理,其实是没有矛盾的,只是有些麻烦,我实在搞不定了,请大家帮忙看看,谢谢!
go
create table [tb]([id] [int] IDENTITY(1,1),[Code] varchar(6),[CreateTime] datetime,[Price] numeric(3,2))
insert [tb]
select '031024','2008-10-10 15:00:00',4.58 union all
select '031024','2008-10-11 15:00:00',5.53 union all
select '031024','2008-10-12 15:00:00',4.54 union all
select '031024','2008-10-13 15:00:00',3.51 union all
select '031024','2008-10-14 15:00:00',3.52 union all
select '031024','2008-10-15 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 union all
select '031025','2008-10-22 15:00:00',2.50 union all
select '031025','2008-10-23 15:00:00',4.53 union all
select '031025','2008-10-24 15:00:00',5.55 union all
select '031025','2008-10-25 15:00:00',5.55 union all
select '031025','2008-10-26 15:00:00',5.57
我想查询后的结果为
031024 2008-10-10 15:00:00 4.58
031024 2008-10-11 15:00:00 5.53
031024 2008-10-12 15:00:00 4.54
031024 2008-10-13 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
031025 2008-10-22 15:00:00 2.50
031025 2008-10-23 15:00:00 4.53
031025 2008-10-24 15:00:00 5.55
031025 2008-10-25 15:00:00 5.55
031025 2008-10-26 15:00:00 5.57这个数据是这样得到的
输入Price开始范围和结束范围
开始条件 Price between 4 and 5
结束条件 Price <4
这个开始与结束就是指每个Code从上往下走,只要符合Price between 4 and 5 就开始,走到Price <4就停止,停止后继续往下走,只要符合Price between 4 and 5 就开始,走到Price <4就停止,直到没有数据为止。
(注意:031025 因为结束条件没有符合Price <4 所以最后的数据就全部列出来)
没有符合结束条件从开始以后的就全部列出来这种情况一定要考虑到。
大家可能会觉得这样的条件有些矛盾不符合常理,其实是没有矛盾的,只是有些麻烦,我实在搞不定了,请大家帮忙看看,谢谢!
----这样的问题,最好用游标,我不看好SQL !
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
----游标方法(修正版)
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;
----------------------------------------------------------------
1 031021 2008-10-17 15:00:00.000 4.58
2 031021 2008-10-18 15:00:00.000 5.53
3 031021 2008-10-19 15:00:00.000 4.54
4 031021 2008-10-20 15:00:00.000 3.51
9 031024 2008-10-18 15:00:00.000 4.53
10 031024 2008-10-19 15:00:00.000 3.55
14 031025 2008-10-18 15:00:00.000 4.53
15 031025 2008-10-19 15:00:00.000 5.55
16 031025 2008-10-20 15:00:00.000 5.55
17 031025 2008-10-21 15:00:00.000 5.57
declare @id int
declare @code varchar(6)
declare @createtime datetime
declare @price numeric(3,2)
declare @flag int
set @flag=0
open cur
fetch next from cur into @id,@code,@createtime,@price
while @@fetch_status=0
begin
if @flag=0 --寻找开始位置
begin
if @price>=4 and @price<=5
begin
select @id,@code,@createtime,@price
set @flag=1
end
end
else --寻找结束位置
begin
if @price<4
begin
select @id,@code,@createtime,@price
set @flag=0
end
else
begin
select @id,@code,@createtime,@price
end
end
fetch next from cur into @id,@code,@createtime,@priceend
close cur
deallocate cur
declare @id int
declare @code varchar(6)
declare @createtime datetime
declare @price numeric(3,2)
declare @flag int
set @flag=0
open cur
fetch next from cur into @id,@code,@createtime,@price
while @@fetch_status=0
begin
if @flag=0 --寻找开始位置
begin
if @price>=4 and @price<=5
begin
select @id,@code,@createtime,@price
set @flag=1
end
end
else --寻找结束位置
begin
if @price<4
begin
select @id,@code,@createtime,@price
set @flag=0
end
else
begin
select @id,@code,@createtime,@price
end
end
fetch next from cur into @id,@code,@createtime,@priceend
close cur
deallocate cur
declare @id int
declare @code varchar(6)
declare @createtime datetime
declare @price numeric(3,2)
declare @flag int
set @flag=0
open cur
fetch next from cur into @id,@code,@createtime,@price
while @@fetch_status=0
begin
if @flag=0 --寻找开始位置
begin
if @price>=4 and @price<=5
begin
insert into @TB
select @id,@code,@createtime,@price
set @flag=1
end
end
else --寻找结束位置
begin
if @price<4
begin
insert into @TB
select @id,@code,@createtime,@price
set @flag=0
end
else
begin
insert into @TB
select @id,@code,@createtime,@price
end
end
fetch next from cur into @id,@code,@createtime,@priceend
close cur
deallocate curselect * from @TB
以下是结果:
1 031024 2008-10-10 15:00:00.000 4.58
2 031024 2008-10-11 15:00:00.000 5.53
3 031024 2008-10-12 15:00:00.000 4.54
4 031024 2008-10-13 15:00:00.000 3.51
9 031024 2008-10-18 15:00:00.000 4.53
10 031024 2008-10-19 15:00:00.000 3.55
14 031025 2008-10-18 15:00:00.000 4.53
15 031025 2008-10-19 15:00:00.000 5.55
16 031025 2008-10-20 15:00:00.000 5.55
17 031025 2008-10-21 15:00:00.000 5.57
18 031025 2008-10-22 15:00:00.000 2.50
19 031025 2008-10-23 15:00:00.000 4.53
20 031025 2008-10-24 15:00:00.000 5.55
21 031025 2008-10-25 15:00:00.000 5.55
22 031025 2008-10-26 15:00:00.000 5.57
with cte as
(select top 1 *, 1 as start from tb where Price between 4 and 5 order by id
union all
select tb.*,case when start=1
then case when tb.Price<4 then case when cte.Price<4 then 0 else 1 end else 1 end
else case when tb.Price between 4 and 5 then 1 else 0 end
end
from tb,cte
where tb.id=cte.id+1
)
select code,CreateTime,Price
from cte
where start=1