帮助编写查询需求的方法 [英] Help to the way to write a query for the requirement

问题描述

我需要编写一个SQL-Server查询，但是我不知道如何解决.我有一个带有数据的表RealtimeData:

I need to write a SQL-Server query but I don't know how to solve. I have a table RealtimeData with data:

 Time                   |    Value
4/29/2009 12:00:00 AM   |   3672.0000
4/29/2009 12:01:00 AM   |   3645.0000
4/29/2009 12:02:00 AM   |   3677.0000
4/29/2009 12:03:00 AM   |   3634.0000
4/29/2009 12:04:00 AM   |   3676.0000      // is EOD of day "4/29/2009"
4/30/2009 12:00:00 AM   |   3671.0000
4/30/2009 12:01:00 AM   |   3643.0000
4/30/2009 12:02:00 AM   |   3672.0000
4/30/2009 12:03:00 AM   |   3634.0000
4/30/2009 12:04:00 AM   |   3632.0000
4/30/2009 12:05:00 AM   |   3672.0000      // is EOD of day "4/30/2009"
5/1/2009 12:00:00 AM    |   3673.0000
5/1/2009 12:01:00 AM    |   3642.0000
5/1/2009 12:02:00 AM    |   3672.0000
5/1/2009 12:03:00 AM    |   3634.0000
5/1/2009 12:04:00 AM    |   3635.0000      // is EOD of day "5/1/2009"

我想获取表中存在的EOD的日期数据. (EOD =一天结束).使用我的样本数据，我将需要重新整理一个表格，如下所示:

I want to get the EOD's data of days which exist in table. (EOD = end of day). With the my sample's data, I will need to reture a table like following:

   Time      |    Value
4/29/2009    |  3676.0000
4/30/2009    |  3672.0000
5/1/2009     |  3635.0000

注意:我写评论是为了让您知道EOD在哪里.而SQL Server是2005版.

Note: I write the comment so that you can know where is EOD. And SQL Server is version 2005.

注意:RealtimeData表中的数据非常大，超过400.000行.请帮我写优化.

Note: the data in RealtimeData table is very large with more than 400.000 rows. Please help me to write in optimization.

请帮助我解决问题.谢谢.

Please help me to solve my problem. Thanks.

推荐答案

WITH RankedRealTimeData AS (
  SELECT *, ROW_NUMBER() OVER (
      PARTITION BY CONVERT(VARCHAR(10), [TIME], 121) 
      ORDER BY Time DESC) AS RN
  FROM RealTimeData
)
SELECT * FROM RankedRealTimeData WHERE RN=1;

这篇关于帮助编写查询需求的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！