按每个项目获取最近的日期值组

Question

表 t1 具有以下结构

|  id  |      from             |  item   |    p    |
    1    2014-03-26 08:00:00       500        9
    2    2014-03-28 14:30:00       500        7
    3    2014-03-29 14:30:00       200        48
    4    2014-04-01 19:00:00       200        51
    5    2014-03-30 23:30:00       500        6

如何为每个项目选择日期最接近现在的记录？

SELECT t1.`from`, t1.item, t1.item 
  FROM t1
WHERE t1.`from` <= NOW()
  ORDER BY `from` DESC
LIMIT 1

这会将结果限制为 1 个项目

而这个

SELECT t1.`from`, t1.item, t1.item 
  FROM t1
WHERE t1.`from` <= NOW()
  GROUP BY item

返回每个元素的最旧记录，而不是最接近现在的记录。

我应该使用什么？

编辑

预期结果 （2014-03-31 15：30：00）

|  id  |      from             |  item   |    p    |
    3    2014-03-29 14:30:00       200        48
    5    2014-03-30 23:30:00       500        6

我得到的结果：

|  id  |      from             |  item   |    p    |
    1    2014-03-26 08:00:00       500        9
    3    2014-03-29 14:30:00       200        48

服务器时间是完美的！

编辑2

SELECT t1.`from`, t1.p, prod_desc.name AS prod
  FROM PROD_DESC
  JOIN 
    (SELECT MAX(t1.`from`) `from`, t1.id
       FROM t1
     GROUP BY prod) t2 USING(`from`, id)
  LEFT JOIN t1
    ON t1.item = PROD_DESC.id_prod
WHERE t1.`from` <= NOW()

Answer 1

DDL...

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(id  INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,`from`             DATETIME NOT NULL
,item   INT NOT NULL
,p    INT NOT NULL
);
INSERT INTO my_table VALUES
(1,'2014-03-26 08:00:00',500,9),
(2,'2014-03-28 14:30:00',500,7),
(3,'2014-03-29 14:30:00',200,48),
(4,'2014-04-01 19:00:00',200,51),
(5,'2014-03-30 23:30:00',500,6);
SELECT * FROM my_table;
+----+---------------------+------+----+
| id | from                | item | p  |
+----+---------------------+------+----+
|  1 | 2014-03-26 08:00:00 |  500 |  9 |
|  2 | 2014-03-28 14:30:00 |  500 |  7 |
|  3 | 2014-03-29 14:30:00 |  200 | 48 |
|  4 | 2014-04-01 19:00:00 |  200 | 51 |
|  5 | 2014-03-30 23:30:00 |  500 |  6 |
+----+---------------------+------+----+

溶液：

SELECT x.* 
  FROM my_table x 
  JOIN 
     ( SELECT item
            , MAX(`from`) max_from 
         FROM my_table 
        WHERE `from` <= NOW() 
        GROUP 
           BY item
     ) y 
    ON y.item = x.item 
   AND y.max_from = x.`from`;
+----+---------------------+------+----+
| id | from                | item | p  |
+----+---------------------+------+----+
|  3 | 2014-03-29 14:30:00 |  200 | 48 |
|  5 | 2014-03-30 23:30:00 |  500 |  6 |
+----+---------------------+------+----+

Answer 2

这可以通过在最大值上使用自连接来完成

SELECT t.*
FROM t1 t 
JOIN 
(SELECT MAX(t1.`from`) `from`,item ,t1.id
FROM t1 t1
WHERE `from` <= NOW()
GROUP BY item 
) t2 USING(`from`,item)

@Strawberry他是对的，关于 where 子句的位置以及连接应该从复合条件USING( ,item)

Answer 3

你试过吗：

。在最后一个问题更改之后，这又是正确的：

SELECT t1.`from`, t1.item, t1.item 
FROM t1
WHERE t1.`from` <= NOW()
GROUP BY item
ORDER BY t1.`from` DESC

条件更改后编辑

SELECT t1.`from`, t1.item, t1.item 
FROM t1
GROUP BY item
ORDER BY ABS(TIMESTAMPDIFF(MICROSECOND,t1.`from`,NOW()))

然后再次编辑以进行最终编辑

这次我有数据库并检查了结果 - 它们与您的期望相匹配！

SELECT t2.`from`, t2.item, t2.item
FROM ( SELECT * FROM t1 WHERE t1.`from` <= NOW() ORDER BY t1.`from` DESC ) AS t2
GROUP BY t2.item

Answer 4

将日期转换为时间戳，然后按与当前时间的差异对它们进行排序 最后的时间戳将结果限制为 1，以便您只得到最接近的结果。