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都有点复杂，一定会有比这更好的方法。

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/5764.html