mysql获取表中最大的日期（最近的日期）详解数据库

表：订单表和订单支付表
订单表：

CREATE TABLE `order` ( 
  `order_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单id', 
  `order_money` decimal(10,2) DEFAULT NULL COMMENT '订单金额', 
  PRIMARY KEY (`order_id`) 
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COMMENT='订单表'; 
 
 
INSERT INTO `order` (`order_id`, `order_money`) VALUES (1, 100.00); 
INSERT INTO `order` (`order_id`, `order_money`) VALUES (2, 200.00); 
INSERT INTO `order` (`order_id`, `order_money`) VALUES (3, 500.00); 
INSERT INTO `order` (`order_id`, `order_money`) VALUES (4, 1000.00);

订单支付表：

CREATE TABLE `order_price` ( 
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id', 
  `order_id` bigint(20) DEFAULT NULL COMMENT '订单id', 
  `pay_money` decimal(10,2) DEFAULT NULL COMMENT '付款金额', 
  `pay_time` datetime DEFAULT NULL COMMENT '支付日期', 
  PRIMARY KEY (`id`) 
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1; 
 
 
INSERT INTO `order_price` (`id`, `order_id`, `pay_money`, `pay_time`) VALUES (1, 1, 100.00, '2018-8-31 14:23:43'); 
INSERT INTO `order_price` (`id`, `order_id`, `pay_money`, `pay_time`) VALUES (2, 2, 30.00, '2018-8-28 14:23:53'); 
INSERT INTO `order_price` (`id`, `order_id`, `pay_money`, `pay_time`) VALUES (3, 2, 70.00, '2018-8-30 14:24:01'); 
INSERT INTO `order_price` (`id`, `order_id`, `pay_money`, `pay_time`) VALUES (4, 3, 250.00, '2018-8-14 14:24:11'); 
INSERT INTO `order_price` (`id`, `order_id`, `pay_money`, `pay_time`) VALUES (5, 3, 250.00, '2018-8-18 14:24:19'); 
INSERT INTO `order_price` (`id`, `order_id`, `pay_money`, `pay_time`) VALUES (6, 4, 250.00, '2018-8-3 14:24:26'); 
INSERT INTO `order_price` (`id`, `order_id`, `pay_money`, `pay_time`) VALUES (7, 4, 300.00, '2018-8-10 14:24:32'); 
INSERT INTO `order_price` (`id`, `order_id`, `pay_money`, `pay_time`) VALUES (8, 4, 300.00, '2018-8-19 14:24:40');

下面是查询语句：
1.查询未付清的订单

SELECT o.* 
FROM `order` o LEFT JOIN (SELECT order_id oi,SUM(pay_money) sp 
                            FROM order_price 
                            GROUP BY order_id) a 
ON o.order_id = a.oi 
WHERE o.order_money > a.sp

2.获取付清订单的最后支付日期

SELECT o.*,a.mpt 
FROM `order` o LEFT JOIN ( 
                            SELECT id,order_id,SUM(pay_money) spm,MAX(pay_time) mpt 
                            FROM order_price 
                            GROUP BY order_id    
                            ) a 
ON o.order_id = a.order_id 
WHERE o.order_money = a.spm

SELECT o.*,b.apt 
FROM `order` o LEFT JOIN (SELECT SUM(a.pay_money) spm,a.order_id aoi,a.pay_time apt 
                          FROM (SELECT * 
                                FROM order_price 
                                ORDER BY pay_time DESC LIMIT 10000000000000) a 
                          GROUP BY a.order_id) b 
ON o.order_id = b.aoi 
WHERE o.order_money = b.spm

上面这两个查询都可以实现第二个问题。这两个sql都有点复杂，一定会有比这更好的方法。