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

2024-10-09 05:42:06

本文实例讲述了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;

哎，终于解决了。。。

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》

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

MySQL中表子查询与关联子查询的基础学习教程

MySQL 表子查询表子查询是指子查询返回的结果集是 N 行 N 列的一个表数据. MySQL 表子查询实例下面是用于例子的两张原始数据表: article 表: blog 表: SQL 如下: SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog) 查询返回结果如下所示: 该 SQL 的意义在于查找 article 表中指定的字段同时也存在于 blog 表中的所有的行(注
MySQL中三种关联查询方式的简单比较

看看下面三个关联查询的 SQL 语句有何区别? SELECT * FROM film JOIN film_actor ON (film.film_id = film_actor.film_id) SELECT * FROM film JOIN film_actor USING (film_id) SELECT * FROM film, film_actor WHERE film.film_id = film_actor.film_id 最大的不同更多是语法糖,但有一些有意思的东西值得关注. 为了
Yii2实现跨mysql数据库关联查询排序功能代码

背景:在一个mysql服务器上(注意:两个数据库必须在同一个mysql服务器上)有两个数据库: memory (存储常规数据表) 中有一个 user 表(记录用户信息) memory_stat (存储统计数据表) 中有一个 user_stat (记录用户统计数据) 现在在 user 表生成的 GridView 列表中展示 user_stat 中的统计数据只需要在User的model类中添加关联 public function getStat() { return $this->hasOne(U
mysql中的跨库关联查询方法

业务场景:关联不同数据库中的表的查询比如说,要关联的表是:机器A上的数据库A中的表A && 机器B上的数据库B中的表B. 这种情况下,想执行"select A.id,B.id from A left join B on ~~~;"那是不可能的,但业务需求不可变,数据库设计不可变,这就蛋疼了.. 解决方案:在机器A上的数据库A中建一个表B... 这当然不是跟你开玩笑啦,我们采用的是基于MySQL的federated引擎的建表方式. 建表语句示例: CREATE TABL
mysql一对多关联查询分页错误问题的解决方法

xml问价中查询数据中包含list,需要使用collection <resultMap id="XX" type="com.XXX.XXXX"> <id column="o_id" jdbcType="BIGINT" property="id" /> <result column="o_user_id" jdbcType="BIGINT"
mysql关联子查询的一种优化方法分析

本文实例讲述了mysql关联子查询的一种优化方法.分享给大家供大家参考,具体如下: 很多时候,在mysql上实现的子查询的性能较差,这听起来实在有点难过.特别有时候,用到IN()子查询语句时,对于上了某种数量级的表来说,耗时多的难以估计.本人mysql知识所涉不深,只能慢慢摸透个中玄机了. 假设有这样的一个exists查询语句: select * from table1 where exists (select * from table2 where id>=30000 and table1.u
mysql实现多表关联统计(子查询统计)示例

本文实例讲述了mysql实现多表关联统计的方法.分享给大家供大家参考,具体如下: 需求: 统计每本书打赏金额,不同时间的充值数据统计,消费统计, 设计四个表,book 书本表,orders 订单表 reward_log打赏表 consume_log 消费表 ,通过book_id与book表关联, 问题: 当关联超过两张表时导致统计时数据重复,只好用子查询查出来,子查询只能查一个字段,这里用CONCAT_WS函数将多个字段其拼接实现: 查询代码如下 SELECT b.id, b.book_
实例讲解Java的MyBatis框架对MySQL中数据的关联查询

mybatis 提供了高级的关联查询功能,可以很方便地将数据库获取的结果集映射到定义的Java Bean 中.下面通过一个实例,来展示一下Mybatis对于常见的一对多和多对一关系复杂映射是怎样处理的. 设计一个简单的博客系统,一个用户可以开多个博客,在博客中可以发表文章,允许发表评论,可以为文章加标签.博客系统主要有以下几张表构成: Author表:作者信息表,记录作者的信息,用户名和密码,邮箱等. Blog表 : 博客表,一个作者可以开多个博客,即Author和Blog的关系是一对多.
浅谈mysql中多表不关联查询的实现方法

大家在使用MySQL查询时正常是直接一个表的查询,要不然也就是多表的关联查询,使用到了左联结(left join).右联结(right join).内联结(inner join).外联结(outer join).这种都是两个表之间有一定关联,也就是我们常常说的有一个外键对应关系,可以使用到 a.id = b.aId这种语句去写的关系了.这种是大家常常使用的,可是有时候我们会需要去同时查询两个或者是多个表的时候,这些表又是没有互相关联的,比如要查user表和user_history表中的某一些数据
MySQL 多表关联一对多查询实现取最新一条数据的方法示例

本文实例讲述了MySQL 多表关联一对多查询实现取最新一条数据的方法.分享给大家供大家参考,具体如下: MySQL 多表关联一对多查询取最新的一条数据遇到的问题多表关联一对多查询取最新的一条数据,数据出现重复由于历史原因,表结构设计不合理:产品告诉我说需要导出客户信息数据,需要导出客户的所属行业,纳税性质数据:但是这两个字段却在订单表里面,每次客户下单都会要求客户填写:由此可知,客户数据和订单数据是一对多的关系:那这样的话,问题就来了,我到底以订单中的哪一条数据为准呢?经过协商后一致同
解析Mybatis对sql表的一对多查询问题

Mybatisd对sql表的一对多查询 select * from projectrecord pr left join projects po on po.pid=pr.pid left join emp e on e.empno = pr.empno where pr.pid=1 <?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.or
Mysql多表关联更新方式

目录 Mysql多表关联更新 Mysql多表关联update 总结 Mysql多表关联更新日常的开发中一般都是写的单表update语句,很少写多表关联的update. 在MySQL中我们有几种方法可以做到多表关联更新: 在两张表之间做关联,可以更新关联的多张表 update biao1,表2 set 表1.列名=表2.列名 --外键或关联字段 where 更新条件:--例如:表1.列名=表2.列名,或字段aa>1等等通过 INNER JOIN,可以更新关联的多张表 update 表1 i
MySQL单表多关键字模糊查询的实现方法

在最近的一个项目需要实现在MySQL单表多关键字模糊查询,但这数个关键字并不一定都存在于某个字段.例如现有table表,其中有title,tag,description三个字段,分别记录一条资料的标题,标签和介绍.然后根据用户输入的查询请求,将输入的字串通过空格分割为多个关键字,再在这三个字段中查询包含这些关键字的记录. 可目前遇到的问题是,这些关键字是可能存在于三个字段中的任意一个或者多个,但又要求三个字段必须包含所有的关键词.如果分别对每个字段进行模糊匹配,是没法实现所需的要求,由此想到两种
为什么Mysql 数据库表中有索引还是查询慢

目录前言: 1.字段类型不匹配导致的索引失效 2.被索引字段使用了表达式计算 3.被索引字段使用了内置函数 4.like 使用了 %X 模糊匹配 5.索引字段不是联合索引字段的最左字段 6.or 分割的条件 7.in.not in 可能会导致索引失效总结前言: 问题分析: 在进行数据库查询的时候,我们都知道索引可以加快数据查询的效率.但是在实际的业务场景下,经常会遇到即使在表中增加了索引,但是同样还是会出现数据查询慢的问题.这就需要具体分析数据查询慢的具体原因到底是什么了. 首先需要进行确
mysql根据json字段内容作为查询条件(包括json数组)检索数据

最近用到了mysql5.7的json字段的检索查询,发现挺好用的,记录一下笔记我们有一个日志表,里面的data字段是保存不同对象的json数据,遇到想根据里面的json的字段内容作为条件查询的情况 mysql根据json字段的内容检索查询数据使用字段->'$.json属性'进行查询条件使用json_extract函数查询,json_extract(字段,"$.json属性") 根据json数组查询,用JSON_CONTAINS(字段,JSON_OBJECT('json属性'
mysql使用GROUP BY分组实现取前N条记录的方法

本文实例讲述了mysql使用GROUP BY分组实现取前N条记录的方法.分享给大家供大家参考,具体如下: MySQL中GROUP BY分组取前N条记录实现 mysql分组,取记录 GROUP BY之后如何取每组的前两位下面我来讲述mysql中GROUP BY分组取前N条记录实现方法. 这是测试表(也不知道怎么想的,当时表名直接敲了个aa,汗~~~~): 结果: 方法一: 复制代码代码如下: SELECT a.id,a.SName,a.ClsNo,a.Score FROM aa a LEFT J
mysql实现合并同一ID对应多条数据的方法

本文实例讲述了mysql实现合并同一ID对应多条数据的方法.分享给大家供大家参考,具体如下: 如 : CREATE TABLE `c_classuser_tab` ( `Id` int(11) NOT NULL AUTO_INCREMENT, `Classid` int(11) DEFAULT NULL, `Username` varchar(100) DEFAULT NULL, `studentid` varchar(100) DEFAULT NULL, `College` varchar(1
Mysql多表关联不走索引的原因及分析

目录一.准备过程二.比较三.千万级别的数据查询个人优化建议四.MYSQL多表查询的区别总结刚入职第一天,有个大佬写了一个统计函数count(*)需要对两张表a,b做统计.咋一看挺简单的,可是表a有1000万条数据,表b有300万条数据.使用LEFT JOIN进行查询.结果,一直查询不出来,可能时间就很久了.然后,这个锅就甩给第一天入职的我(我???). 接下来,就研究一下如何对海量数据的查询进行优化. 一.准备过程 1.创建两张表,表A large_student_tb(幼儿园大班学

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

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

遇到的问题

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

相关推荐

随机推荐