php - 按项目获取最近的日期值组

表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()

最佳答案

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 |
+----+---------------------+------+----+

关于php - 按项目获取最近的日期值组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/22762809/