CREATE TABLE `order_info` (
`id` int(2) DEFAULT NULL,
`user_id` int(2) DEFAULT NULL,
`buy_time` date DEFAULT NULL,
`last_time` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO order_info VALUES (1,11,'2013-10-16',NULL);
INSERT INTO order_info VALUES (2,11,'2013-10-17',NULL);
INSERT INTO order_info VALUES (3,11,'2013-10-18',NULL);
INSERT INTO order_info VALUES (4,11,'2013-10-19',NULL);
INSERT INTO order_info VALUES (5,22,'2013-10-22',NULL);
INSERT INTO order_info VALUES (6,22,'2013-10-25',NULL);
INSERT INTO order_info VALUES (7,22,'2013-10-28',NULL);
-- 查询当前用户的上次购买时间
select id,user_id,buy_time,(select max(buy_time) from order_info as a where a.user_id = oi.user_id and a.id<oi.id) AS last_time
from order_info as oi;
除了嵌套子查询还有其他方法吗? 几百万数据的查询