MySQL 多表关联一对多查询实现取最新一条数据的方法示例

本文实例讲述了MySQL 多表关联一对多查询实现取最新一条数据的方法。分享给大家供大家参考，具体如下：

MySQL 多表关联一对多查询取最新的一条数据

 

遇到的问题

多表关联一对多查询取最新的一条数据，数据出现重复

由于历史原因，表结构设计不合理；产品告诉我说需要导出客户信息数据，需要导出客户的 所属行业，纳税性质 数据；但是这两个字段却在订单表里面，每次客户下单都会要求客户填写；由此可知，客户数据和订单数据是一对多的关系；那这样的话，问题就来了，我到底以订单中的哪一条数据为准呢？经过协商后一致同意以最新的一条数据为准；

数据测试初始化SQL脚本

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67

DROP TABLE IF EXISTS `customer`; CREATE TABLE `customer` (      `id` BIGINT NOT NULL COMMENT '客户ID' ,      `real_name` VARCHAR (20) NOT NULL COMMENT '客户名字' ,      `create_time` DATETIME NOT NULL COMMENT '创建时间' ,      PRIMARY KEY (`id`) )ENGINE=INNODB DEFAULT CHARSET = UTF8 COMMENT '客户信息表' ;   -- DATA FOR TABLE customer INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7717194510959685632' , '张三' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7718605481599623168' , '李四' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7720804666226278400' , '王五' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7720882041353961472' , '刘六' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7722233303626055680' , '宝宝' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7722233895811448832' , '小宝' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7722234507982700544' , '大宝' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7722234927631204352' , '二宝' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7722235550724423680' , '小贱' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7722235921488314368' , '小明' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7722238233975881728' , '小黑' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7722246644138409984' , '小红' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7722318634321346560' , '阿狗' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7722318674321346586' , '阿娇' , '2019-01-23 16:23:05' ); INSERT INTO `demo`.`customer` (`id`, `real_name`, `create_time`) VALUES ( '7722318974421546780' , '阿猫' , '2019-01-23 16:23:05' );     DROP TABLE IF EXISTS `order_info`; CREATE TABLE `order_info` (      `id` BIGINT NOT NULL COMMENT '订单ID' ,      `industry` VARCHAR (255) DEFAULT NULL COMMENT '所属行业' ,   `nature_tax` VARCHAR (255) DEFAULT NULL COMMENT '纳税性质' ,      `customer_id` VARCHAR (20) NOT NULL COMMENT '客户ID' ,      `create_time` DATETIME NOT NULL COMMENT '创建时间' ,      PRIMARY KEY (`id`) )ENGINE=INNODB DEFAULT CHARSET = UTF8 COMMENT '订单信息表' ;   -- DATA FOR TABLE order_info INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7700163609453207552' , '餐饮酒店类' , '小规模' , '7717194510959685632' , '2019-01-23 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7700163609453207553' , '餐饮酒店类' , '小规模' , '7717194510959685632' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7700167995646615552' , '高新技术' , '一般纳税人' , '7718605481599623168' , '2019-01-23 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7700167995646615553' , '商贸' , '一般纳税人' , '7718605481599623168' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7700193633216569344' , '商贸' , '一般纳税人' , '7720804666226278400' , '2019-01-23 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7700193633216569345' , '高新技术' , '一般纳税人' , '7720804666226278400' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7700197875671179264' , '餐饮酒店类' , '一般纳税人' , '7720882041353961472' , '2019-01-23 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7700197875671179266' , '餐饮酒店类' , '一般纳税人' , '7720882041353961472' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7703053372673171456' , '高新技术' , '小规模' , '7722233303626055680' , '2019-01-23 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7703053372673171457' , '高新技术' , '小规模' , '7722233303626055680' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709742385262698496' , '服务类' , '一般纳税人' , '7722233895811448832' , '2019-01-23 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709742385262698498' , '服务类' , '一般纳税人' , '7722233895811448832' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745055683780608' , '高新技术' , '小规模' , '7722234507982700544' , '2019-01-23 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745055683780609' , '进出口' , '小规模' , '7722234507982700544' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745249439653888' , '文化体育' , '一般纳税人' , '7722234927631204352' , '2019-01-24 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745249439653889' , '高新技术' , '一般纳税人' , '7722234927631204352' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745453266051072' , '高新技术' , '小规模' , '7722235550724423680' , '2019-01-24 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745453266051073' , '文化体育' , '小规模' , '7722235550724423680' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745539848413184' , '科技' , '一般纳税人' , '7722235921488314368' , '2019-01-24 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745539848413185' , '高新技术' , '一般纳税人' , '7722235921488314368' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745652603887616' , '高新技术' , '一般纳税人' , '7722238233975881728' , '2019-01-24 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745652603887617' , '科技' , '一般纳税人' , '7722238233975881728' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745755528568832' , '进出口' , '一般纳税人' , '7722246644138409984' , '2019-01-24 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745755528568833' , '教育咨询' , '小规模' , '7722246644138409984' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745892539047936' , '教育咨询' , '一般纳税人' , '7722318634321346560' , '2019-01-24 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709745892539047937' , '进出口' , '一般纳税人' , '7722318634321346560' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709746000127139840' , '生产类' , '小规模' , '7722318674321346586' , '2019-01-24 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709746000127139841' , '农业' , '一般纳税人' , '7722318674321346586' , '2019-01-23 17:09:53' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709746447445467136' , '农业' , '一般纳税人' , '7722318974421546780' , '2019-01-24 16:54:25' ); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ( '7709746447445467137' , '生产类' , '小规模' , '7722318974421546780' , '2019-01-23 17:09:53' );

• 按需求写的SQL语句：

?

1	UPDATE order_info SET create_time = NOW();

• 尝试解决问题

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT      cr.id,      cr.real_name,      oi.industry,      oi.nature_tax FROM      customer AS cr LEFT JOIN (      SELECT a.industry, a.nature_tax, a.customer_id, a.create_time FROM order_info AS a      LEFT JOIN (          SELECT MAX (create_time) AS create_time, customer_id FROM order_info GROUP BY customer_id      ) AS b ON a.customer_id = b.customer_id WHERE a.create_time = b.create_time ) AS oi ON oi.customer_id = cr.id GROUP BY cr.id;

数据重复嘛，小意思，加个 GROUP BY 不就解决了吗？我怎么会这么机智，哈哈哈！！！但是当我执行完SQL的那一瞬间，我又懵逼了，查询出来的结果中 所属行业，纳税性质 仍然不是最新的；看来是我想太多了，还是老老实实的解决问题吧。。。

• 找出重复数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT      cr.id,      cr.real_name,      oi.industry,      oi.nature_tax FROM      customer AS cr LEFT JOIN (      SELECT a.industry, a.nature_tax, a.customer_id, a.create_time FROM order_info AS a      LEFT JOIN (          SELECT MAX (create_time) AS create_time, customer_id FROM order_info GROUP BY customer_id      ) AS b ON a.customer_id = b.customer_id WHERE a.create_time = b.create_time ) AS oi ON oi.customer_id = cr.id GROUP BY cr.id HAVING COUNT (cr.id) >= 2;

• 执行结果如下：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT      cr.id,      cr.real_name,      oi.industry,      oi.nature_tax FROM      customer AS cr LEFT JOIN (      SELECT a.industry, a.nature_tax, a.customer_id, a.create_time FROM order_info AS a      LEFT JOIN (          SELECT MAX (id) AS id, customer_id FROM order_info GROUP BY customer_id      ) AS b ON a.customer_id = b.customer_id WHERE a.id = b.id ) AS oi ON oi.customer_id = cr.id;

哎，终于解决了。。。

希望本文所述对大家MySQL数据库计有所帮助。

原文链接：https://blog.csdn.net/u013902368/article/details/86615382