数据库值得比较解决办法

数据库值得比较
有一个数据库表Test：
name types state times
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

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T1') is null
    drop table #T1
Go
Create table #T1([name] nvarchar(1),[types] nvarchar(2),[state] int,[times] Datetime)
Insert #T1
select N'A',N'AA',1,'2011-12-30 09:45:54' union all
select N'A',N'AA',1,'2011-12-30 09:45:54' union all
select N'A',N'AA',1,'2011-12-30 09:45:54' union all
select N'A',N'BB',1,'2011-12-30 09:45:54' union all
select N'A',N'BB',1,'2011-12-30 09:45:54' union all
select N'A',N'BB',1,'2011-12-30 09:45:54' union all
select N'A',N'BB',1,'2011-12-30 11:45:54' union all
select N'A',N'AA',1,'2011-12-30 11:45:54' union all
select N'A',N'AA',2,'2011-12-30 11:45:54' union all
select N'A',N'AA',1,'2011-12-30 11:45:54' union all
select N'A',N'BB',1,'2011-12-30 11:45:54' union all
select N'A',N'BB',2,'2011-12-30 11:45:54' union all
select N'A',N'BB',1,'2011-12-30 11:45:54' union all
select N'A',N'BB',2,'2011-12-30 11:45:54'
Go
Select * ,[times2]=(SELECT MAX([times]) FROM #T1 where [name]=a.[name] AND [types]=a.[types] AND DATEDIFF(n,[times],a.[times])<120 AND state=1)
from #T1 AS a
WHERE [state]=2
/*
name    types    state    times    times2
A    AA    2    2011-12-30 11:45:54.000    2011-12-30 11:45:54.000
A    BB    2    2011-12-30 11:45:54.000    2011-12-30 11:45:54.000

A    BB    2    2011-12-30 11:45:54.000    2011-12-30 11:45:54.000*/

------解决方案--------------------

SQL code



create table tables
(  a      nvarchar,
   b      int,
   c     nvarchar)

insert into  tables 


Select  'A',2,'1'
     union 
select 'A',3,'2'
union 
SELECT 'a',1,'3'




select  b.* from  
(select a ,max(b) s from  tables 
              group by a )a,
(select  * from  tables) b
where a.a=b.a and a.s=b.b

数据库值得比较解决办法

相关推荐