使用存储过程比较值,该如何解决

使用存储过程比较值
有一个数据库表Test：
name types state times
A AA 1 2011-12-30 09:35:54
A AA 1 2011-12-30 09:35:54
A AA 1 2011-12-30 09:35:54
A BB 1 2011-12-30 09:35:54
A BB 1 2011-12-30 09:35:54
A BB 1 2011-12-30 09:35:54
A AA 1 2011-12-30 09:45:54
A AA 1 2011-12-30 09:45:54
A AA 1 2011-12-30 09:45:54
A BB 1 2011-12-30 09:45:54
A BB 1 2011-12-30 09:45:54
A BB 1 2011-12-30 09:45:54
A BB 1 2011-12-30 11:45:54
A AA 1 2011-12-30 11:45:54
A AA 2 2011-12-30 11:45:54
A AA 1 2011-12-30 11:45:54
A BB 1 2011-12-30 11:45:54
A BB 2 2011-12-30 11:45:54
A BB 1 2011-12-30 11:45:54
A BB 2 2011-12-30 11:45:54
要求：查询当前最大时间的数据和两小时前的最大时间的数据相比较，如果两小时前的state为1，而现在state为2，则显示现在最大时间时的值，上面的数据通过查询应显示成如下结果：

name types state times
A AA 2 2011-12-30 11:45:54
A BB 2 2011-12-30 11:45:54
A BB 2 2011-12-30 11:45:54
 


[解决办法]

SQL code

create proc getMaxTime(   @hour int  --时间间隔(单位:小时))asbegin declare @time1 datetime declare @time2 datetime declare @state1 int declare @state2 int--查询当前最大时间记录 select  @time1=max(times), @state1=state from test group by name--查询hour小时前最大时间记录 select  @time2=max(times), @state2=state from test where datediff(hh,times,getdate())>@hour group by name  if(@time1 is not null and @time2 is not null) begin   if(@state1==2 and @state2==1)   begin     return @time1   end   else   begin     return @time2   end   --相应的其他判断并返回数据 endend
[解决办法]
SQL code
--> 测试数据：#if object_id('tempdb.dbo.#') is not null drop table #create table #(name varchar(8), types varchar(8), state int, times datetime)insert into #select 'A', 'AA', 1, '2011-12-30 09:35:54' union allselect 'A', 'AA', 1, '2011-12-30 09:35:54' union allselect 'A', 'AA', 1, '2011-12-30 09:35:54' union allselect 'A', 'BB', 1, '2011-12-30 09:35:54' union allselect 'A', 'BB', 1, '2011-12-30 09:35:54' union allselect 'A', 'BB', 1, '2011-12-30 09:35:54' union allselect 'A', 'AA', 1, '2011-12-30 09:45:54' union allselect 'A', 'AA', 1, '2011-12-30 09:45:54' union allselect 'A', 'AA', 1, '2011-12-30 09:45:54' union allselect 'A', 'BB', 1, '2011-12-30 09:45:54' union allselect 'A', 'BB', 1, '2011-12-30 09:45:54' union allselect 'A', 'BB', 1, '2011-12-30 09:45:54' union allselect 'A', 'BB', 1, '2011-12-30 11:45:54' union allselect 'A', 'AA', 1, '2011-12-30 11:45:54' union allselect 'A', 'AA', 2, '2011-12-30 11:45:54' union allselect 'A', 'AA', 1, '2011-12-30 11:45:54' union allselect 'A', 'BB', 1, '2011-12-30 11:45:54' union allselect 'A', 'BB', 2, '2011-12-30 11:45:54' union allselect 'A', 'BB', 1, '2011-12-30 11:45:54' union allselect 'A', 'BB', 2, '2011-12-30 11:45:54'select * from # t where state=2 and exists (select 1 from # where types=t.types and state=1 and datediff(hour,times,t.times)=2)/*name types state timesA AA 2 2011-12-30 11:45:54A BB 2 2011-12-30 11:45:54A BB 2 2011-12-30 11:45:54*/