27,580
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-09-10 13:44:26
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([MeasureTime] datetime,[Symbol] int,[Result] int,[Conference] int)
insert [huang]
select '2013-2-19 13:53:15',1,673,384 union all
select '2013-2-19 13:53:15',2,670,376 union all
select '2013-2-19 13:53:15',3,625,551 union all
select '2013-2-19 13:53:15',4,0,0 union all
select '2013-2-19 13:53:15',5,510,420 union all
select '2013-2-19 13:53:15',6,496,405 union all
select '2013-2-19 13:53:15',7,536,410 union all
select '2013-2-19 13:53:15',8,520,340 union all
select '2013-2-19 13:53:15',9,284,150 union all
select '2013-2-19 15:30:32',1,383,282 union all
select '2013-2-19 15:30:32',2,210,150 union all
select '2013-2-19 15:30:32',3,272,147 union all
select '2013-2-19 15:30:32',4,250,152 union all
select '2013-2-19 15:30:32',5,261,163 union all
select '2013-2-19 15:30:32',6,568,408 union all
select '2013-2-19 15:30:32',7,568,408 union all
select '2013-2-19 16:03:01',1,611,407 union all
select '2013-2-19 16:03:01',2,629,388 union all
select '2013-2-19 16:03:01',3,606,401 union all
select '2013-2-19 16:03:01',4,641,411 union all
select '2013-2-19 16:03:01',5,638,409 union all
select '2013-2-19 16:03:01',6,693,411 union all
select '2013-2-19 17:13:15',1,432,288 union all
select '2013-2-19 17:13:15',2,0,0 union all
select '2013-2-19 17:13:15',3,481,395 union all
select '2013-2-19 17:13:15',4,419,319
--------------开始查询--------------------------
DECLARE @n INT
SET @n=2
;WITH cte AS (
SELECT TOP (@n) *
FROM (
select DISTINCT MeasureTime,DATEDIFF(ss,MeasureTime,GETDATE())Diff
from [huang])a
ORDER BY Diff )
SELECT *
FROM dbo.huang a
WHERE EXISTS (
SELECT 1 FROM cte b WHERE a.measureTime=b.measureTime)
----------------结果----------------------------
/*
MeasureTime Symbol Result Conference
----------------------- ----------- ----------- -----------
2013-02-19 16:03:01.000 1 611 407
2013-02-19 16:03:01.000 2 629 388
2013-02-19 16:03:01.000 3 606 401
2013-02-19 16:03:01.000 4 641 411
2013-02-19 16:03:01.000 5 638 409
2013-02-19 16:03:01.000 6 693 411
2013-02-19 17:13:15.000 1 432 288
2013-02-19 17:13:15.000 2 0 0
2013-02-19 17:13:15.000 3 481 395
2013-02-19 17:13:15.000 4 419 319
*/