sql select出来的是一个集合的概念,并不是一个值,所以没办法用等于,这边你可以改用in的方式select type_id, int_value, start_time, end_time, (end_time-start_time) from [dbo].[OEE] where type_id=2 and int_value=128 and start_time in (select start_time from [dbo].[OEE] as t1 where t1.type_id=10 and t1.int_value in(1,2,4) and t1.start_time>1286885699 and t1.end_time<1286921701) and end_time in (select end_time from [dbo].[OEE] as t2 where t2.type_id=10 and t2.int_value in (1,2,4) and t2.start_time>1286885699 and t2.end_time<1286921701)
那你可以将top 1的写入到变量中,然后再用变量使用等于操作进行查询 declare @start_time int , @end_time int select @start_time = start_time , @end_time = end_time from [dbo].[OEE] as t1 where t1.type_id=10 and t1.int_value in(1,2,4) and t1.start_time>1286885699 and t1.end_time<1286921701 select type_id, int_value, start_time, end_time, (end_time-start_time) from [dbo].[OEE] where type_id=2 and int_value=128 and start_time=@start_time and end_time=@end_time)
也就是说,怎么规定下一次的查询时间点 start time 大于t1, endtime 小于 t2,依次类推,再下一次的start time和end time要在第二行的范围之内,有办法实现吗?
declare @start_time int , @end_time int select top 1 @start_time = start_time , @end_time = end_time from [dbo].[OEE] as t1 where t1.type_id=10 and t1.int_value in(1,2,4) and t1.start_time>1286885699 and t1.end_time<1286921701 select type_id, int_value, start_time, end_time, (end_time-start_time) from [dbo].[OEE] where type_id=2 and int_value=128 and start_time=@start_time and end_time=@end_time)
declare @start_time int , @end_time int select @start_time = start_time , @end_time = end_time from [dbo].[OEE] as t1 where t1.type_id=10 and t1.int_value in(1,2,4) and t1.start_time>1286885699 and t1.end_time<1286921701 select type_id, int_value, start_time, end_time, (end_time-start_time) from [dbo].[OEE] where type_id=2 and int_value=128 and start_time=@start_time and end_time=@end_time)汗,代码中没办法再使用颜色了
也就是说,怎么规定下一次的查询时间点 start time 大于t1, endtime 小于 t2,依次类推,再下一次的start time和end time要在第二行的范围之内,有办法实现吗? 如果你要这样循环的话要用到游标 你可以参考下面的文章试着自己写看看 http://blog.csdn.net/luminji/article/details/5130004 go declare @t1 integer, @t2 integer declare OEE_time1 cursor for select start_time, end_time from [dbo].[OEE] where end_time<1286921701 and start_time>1286885699 and type_id=10 and int_value in (1,2,4) order by start_time, end_time open OEE_time1 FETCH NEXT from OEE_time1 INTO @t1, @t2 WHILE @@FETCH_STATUS = 0 BEGIN select start_time, end_time, (end_time-start_time) from [dbo].[OEE] where start_time >@t1 and end_time<@t2 and type_id=2 and int_value=128 FETCH NEXT from OEE_time1 into @t1, @t2 END close OEE_time1 DEALLOCATE OEE_time1 go 写了上面的代码,如果要把循环出来的结果再一起相加,还要写什么呢?
go declare @t1 integer, @t2 integer declare @sum integer --定义一个存储相加结果的变量 declare OEE_time1 cursor for select start_time, end_time from [dbo].[OEE] where end_time<1286921701 and start_time>1286885699 and type_id=10 and int_value in (1,2,4) order by start_time, end_time open OEE_time1 FETCH NEXT from OEE_time1 INTO @t1, @t2 WHILE @@FETCH_STATUS = 0 BEGIN select @sum = isnull(@sum,0) + sum((end_time-start_time)) from [dbo].[OEE] where start_time >@t1 and end_time<@t2 and type_id=2 and int_value=128 --每次取出加总的值进行累加 FETCH NEXT from OEE_time1 into @t1, @t2 END close OEE_time1 DEALLOCATE OEE_time1 go
where type_id=2 and int_value=128
and start_time in (select start_time from [dbo].[OEE] as t1 where t1.type_id=10 and t1.int_value in(1,2,4) and t1.start_time>1286885699 and t1.end_time<1286921701) and
end_time in (select end_time from [dbo].[OEE] as t2 where t2.type_id=10 and t2.int_value in (1,2,4) and t2.start_time>1286885699 and t2.end_time<1286921701)
declare @start_time int , @end_time int
select @start_time = start_time , @end_time = end_time from [dbo].[OEE] as t1 where t1.type_id=10 and t1.int_value in(1,2,4) and t1.start_time>1286885699 and t1.end_time<1286921701
select type_id, int_value, start_time, end_time, (end_time-start_time) from [dbo].[OEE]
where type_id=2 and int_value=128
and start_time=@start_time and
end_time=@end_time)
declare @start_time int , @end_time int
select top 1 @start_time = start_time , @end_time = end_time from [dbo].[OEE] as t1 where t1.type_id=10 and t1.int_value in(1,2,4) and t1.start_time>1286885699 and t1.end_time<1286921701
select type_id, int_value, start_time, end_time, (end_time-start_time) from [dbo].[OEE]
where type_id=2 and int_value=128
and start_time=@start_time and
end_time=@end_time)
declare @start_time int , @end_time int
select @start_time = start_time , @end_time = end_time from [dbo].[OEE] as t1 where t1.type_id=10 and t1.int_value in(1,2,4) and t1.start_time>1286885699 and t1.end_time<1286921701
select type_id, int_value, start_time, end_time, (end_time-start_time) from [dbo].[OEE]
where type_id=2 and int_value=128
and start_time=@start_time and
end_time=@end_time)汗,代码中没办法再使用颜色了
如果你要这样循环的话要用到游标
你可以参考下面的文章试着自己写看看
http://blog.csdn.net/luminji/article/details/5130004
如果你要这样循环的话要用到游标
你可以参考下面的文章试着自己写看看
http://blog.csdn.net/luminji/article/details/5130004
go
declare @t1 integer, @t2 integer
declare OEE_time1 cursor for
select start_time, end_time from [dbo].[OEE]
where end_time<1286921701 and start_time>1286885699 and type_id=10 and int_value in (1,2,4)
order by start_time, end_time
open OEE_time1
FETCH NEXT from OEE_time1
INTO @t1, @t2
WHILE @@FETCH_STATUS = 0
BEGIN
select start_time, end_time, (end_time-start_time) from [dbo].[OEE]
where start_time >@t1 and end_time<@t2 and type_id=2 and int_value=128
FETCH NEXT from OEE_time1
into @t1, @t2
END
close OEE_time1
DEALLOCATE OEE_time1
go
写了上面的代码,如果要把循环出来的结果再一起相加,还要写什么呢?
go
declare @t1 integer, @t2 integer
declare @sum integer --定义一个存储相加结果的变量
declare OEE_time1 cursor for
select start_time, end_time from [dbo].[OEE]
where end_time<1286921701 and start_time>1286885699 and type_id=10 and int_value in (1,2,4)
order by start_time, end_time
open OEE_time1
FETCH NEXT from OEE_time1
INTO @t1, @t2
WHILE @@FETCH_STATUS = 0
BEGIN
select @sum = isnull(@sum,0) + sum((end_time-start_time)) from [dbo].[OEE]
where start_time >@t1 and end_time<@t2 and type_id=2 and int_value=128 --每次取出加总的值进行累加
FETCH NEXT from OEE_time1
into @t1, @t2
END
close OEE_time1
DEALLOCATE OEE_time1
go