数据库值得比较

吃兔子的萝卜_110 2011-12-30 12:05:12

有一个数据库表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

...全文

62 3 打赏收藏转发到动态举报

写回复

用AI写文章

3 条回复

切换为时间正序

请发表友善的回复…

发表回复

lcqtgb 2011-12-30

打赏
举报





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

中国风 2011-12-30

打赏
举报

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*/