选择范围内最大值的行

Question

我有以下数据：

+----+---------+---------------------+-------+
| id | site_id |      datetime       | views |
+----+---------+---------------------+-------+
|  1 |       1 | 2013-11-01 23:59:59 |    12 |
|  2 |       1 | 2013-11-02 23:59:59 |    15 |
|  3 |       1 | 2013-11-03 23:59:59 |    18 |
|  4 |       1 | 2013-11-04 23:59:59 |    29 |
|  5 |       1 | 2013-11-05 23:59:59 |    38 |
|  6 |       1 | 2013-11-05 12:59:59 |    40 |
|  7 |       1 | 2013-11-06 23:59:59 |    45 |
|  8 |       1 | 2013-11-07 23:59:59 |    49 |
|  9 |       1 | 2013-11-08 23:59:59 |    52 |
| 10 |       2 | 2013-11-04 23:59:59 |    25 |
| 11 |       2 | 2013-11-05 21:59:59 |    42 |
| 12 |       2 | 2013-11-06 23:59:59 |    60 |
| 13 |       2 | 2013-11-07 23:59:59 |    75 |
| 14 |       2 | 2013-11-08 23:59:59 |    86 |
| 15 |       2 | 2013-11-09 23:59:59 |    90 |
| 16 |       2 | 2013-11-10 23:59:59 |    92 |
| 17 |       2 | 2013-11-11 23:42:59 |    98 |
+----+---------+---------------------+-------+

我会度过一天，并希望得到在给定的一天中时间最高的行，或者如果这个站点没有记录的话，这是过去可用的最后一行。

例如对于2013-11-01

+----+---------+---------------------+-------+
| id | site_id |      datetime       | views |
+----+---------+---------------------+-------+
|  1 |       1 | 2013-11-01 23:59:59 |    12 |
+----+---------+---------------------+-------+

对于2013-11-05

+----+---------+---------------------+-------+
| id | site_id |      datetime       | views |
+----+---------+---------------------+-------+
|  5 |       1 | 2013-11-05 23:59:59 |    38 |
| 11 |       2 | 2013-11-05 21:59:59 |    42 |
+----+---------+---------------------+-------+

对于2013-11-10

+----+---------+---------------------+-------+
| id | site_id |      datetime       | views |
+----+---------+---------------------+-------+
|  9 |       1 | 2013-11-08 23:59:59 |    52 |
| 16 |       2 | 2013-11-10 23:59:59 |    92 |
+----+---------+---------------------+-------+

提前谢谢。

Answer 1

SELECT * FROM <tablename> WHERE datetime = <datetime> ORDER BY datetime DESC LIMIT 2

Answer 2

我认为这样做是可行的：

SELECT SUBSTRING(`datetime`, 1, 10) AS date, MAX(`views`)
FROM table
GROUP BY SUBSTRING(`datetime`, 1, 10)

如果您也需要id/site_id，请将它写在您的帖子中，因为它不清楚

SQLFiddle向您显示结果：http://sqlfiddle.com/#!2/e0ccb3/1

Answer 3

您的数据和示例并不完全匹配，也不符合描述，但您可能需要的是：

Select top 1 * 
  from table
 where date(datetime) <= date(@PARAMETER)

按日期分列的顺序c