Question

我有这个结果设置片段,

DateAndTime         Date        Hr  Min Tag Val
11/15/2011 0:00:29  11/15/2011  0   0   47  0.084000006318092
11/15/2011 0:00:59  11/15/2011  0   0   47  0.086000002920628
11/15/2011 0:00:13  11/15/2011  0   0   47  0.084000006318092
11/15/2011 0:00:44  11/15/2011  0   0   47  0.087000004947186
11/15/2011 0:05:06  11/15/2011  0   5   47  0.088500000536442
11/15/2011 0:05:21  11/15/2011  0   5   47  0.084500007331371
11/15/2011 0:05:51  11/15/2011  0   5   47  0.086000002920628
11/15/2011 0:05:36  11/15/2011  0   5   47  0.088000006973743
11/15/2011 0:10:41  11/15/2011  0   10  47  0.086000002920628
11/15/2011 0:10:56  11/15/2011  0   10  47  0.089500002563000
11/15/2011 0:10:09  11/15/2011  0   10  47  0.086500003933907
11/15/2011 0:10:25  11/15/2011  0   10  47  0.089000001549721
11/15/2011 0:15:45  11/15/2011  0   15  47  0.089000001549721
11/15/2011 0:15:14  11/15/2011  0   15  47  0.087500005960465
11/15/2011 0:15:29  11/15/2011  0   15  47  0.089500002563000
11/15/2011 0:20:48  11/15/2011  0   20  47  0.106000006198883
11/15/2011 0:20:03  11/15/2011  0   20  47  0.109500005841255
11/15/2011 0:20:18  11/15/2011  0   20  47  0.104500003159046
11/15/2011 0:20:33  11/15/2011  0   20  47  0.109000004827976

我只想回回一排 0分, 5分, 5分, 10分,等等

Answer 1

我认为以下内容应在所有数据库管理系统中有效(不过,目前我无法测试):

SELECT actual.DateAndTime, actual.Tag, actual.Val
FROM ResultTable actual
LEFT JOIN ResultTable exclude
ON exclude.Date = actual.Date
AND exclude.Hr = actual.Hr
AND exclude.Min = actual.Min
AND exclude.DateAndTime > actual.DateAndTime
WHERE exclude.DateAndTime IS NULL

...应该能把每一分钟的最新行数净化出来

Answer 2

DECLARE @Minutes VARCHAR(1000) 
;WITH MyRecords AS
(
   SELECT TOP 3 Distinct Min FROM TableName
)


SELECT @Minutes = COALESCE(@Minutes +  ,  ,   ) + MINFROM MyRecords 

PRINT @Minutes

结果:0、5、10

如果您需要添加分钟, 您可以在 @ minutes 之前轻松添加 :)

Answer 3

这是您想要窗口函数的例子, 但是在 SQL 服务器 2000 中无法找到这些功能。

以下是另一个选择:

select t.*
from t join
     (select date, hour, min, min(datetime) as mindatetime
      from t
      group by date, hour, min
     ) tsum
     on t.datetime = tsum.mindatetime

此假设日期字段是独一无二的。否则, 如果您在列中有一个 ID, 您可以代之以使用它。

Answer 4

SELECT *, 
       id = IDENTITY(INT, 1, 1) 
INTO   #temp 
FROM   MyTable 
ORDER  BY [min] 

SELECT o.* 
FROM   #temp o 
WHERE  o.id = (SELECT Min(id) 
               FROM   #temp t 
               WHERE  t.[Min] = o.[Min])

友情链接