mysql获取表中最大的日期(最近的日期)分析

访客 阅读:509 2020-05-25 21:33:40 评论:0

表:订单表和订单支付表
订单表:

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

标签:mysql
声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

发表评论
搜索
排行榜
关注我们

扫一扫关注我们,了解最新精彩内容