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

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

我如何才能为每个项目选择日期最近的记录 IN THE PAST？

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

服务器时间完美!

EDIT2

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