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

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

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

遇到的问题

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

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

数据测试初始化sql脚本

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语句：

update order_info set create_time = now();

• 尝试解决问题

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的那一瞬间，我又懵逼了，查询出来的结果中 所属行业，纳税性质 仍然不是最新的；看来是我想太多了，还是老老实实的解决问题吧。。。

• 找出重复数据

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;

• 执行结果如下：

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;

哎，终于解决了。。。