如何访问最近两个日期的 SQL Server

Question

>我在表格中有日期列

ColumnName: event_timestamp
2015-06-01 15:23:31.000
2015-06-01 15:25:21.000
2015-06-03 09:00:41.000
2015-06-03 09:14:49.000
2015-06-03 09:15:03.000
2015-06-03 09:15:23.000
2015-06-06 08:40:06.000
2015-06-06 08:40:19.000
2015-06-06 11:13:35.000
2015-06-06 11:13:53.000
2015-06-06 11:15:04.000
2015-06-06 11:15:30.000
2015-06-09 15:08:13.000
2015-06-09 15:08:33.000
2015-06-09 15:08:45.000
2015-06-09 15:09:05.000

预期输出

2015-06-06
2015-06-09

我试过但失败了

SELECT CONVERT(datetime,MAX(myDate),103),      
               (MAX(CONVERT(datetime,mydate,103)))-1 as DT 
FROM (
       SELECT DISTINCT (CONVERT(VARCHAR,events.event_timestamp, 103)) myDate
       FROM events 
       Where event_timestamp Between '01-Jun-15 11:14:40 AM' 
                     AND '11-Jun-15 11:14:40 AM'
     ) tbl

Answer 1

将分组依据和排序依据子句与 Desc 一起使用，然后获取前 2 条记录

Select TOP 2 CONVERT(date, datecol) 
From #t
Group By CONVERT(date, datecol)
Order By CONVERT(date, datecol) Desc

查看工作示例

Answer 2

你可以

这样做：

-- grabs top 2 descending by date
select top 2 distinctDates
from (
    -- gets the distinct dates between your date range
    select distinct convert(date, mydate) as distinctDates
    FROM events e
    where e.timestamp Between '01-Jun-15 11:14:40 AM' AND '11-Jun-15 11:14:40 AM'
) dt
order by distinctDates desc

Answer 3

一个简单的方法是做一个"GROUP BY"和一个"LIMIT 2"。 您需要按日期分组（请记住忽略时间），然后将结果限制为 2。 这是这两个链接。

限制 ： http://www.w3schools.com/sql/sql_top.asp分组依据 ： https://dev.mysql.com/doc/refman/5.5/en/group-by-handling.html