MySQL 多表关联一对多查询实现取最新一条数据的方法示例
作者:大爱我小宝哥 时间:2024-01-22 13:58:17
本文实例讲述了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数据库计有所帮助。
来源:https://blog.csdn.net/u013902368/article/details/86615382
标签:MySQL,多表关联,一对多查询
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
python实现输入三角形边长自动作图求面积案例
2023-08-12 01:47:49
![](https://img.aspxhome.com/file/2023/7/94177_0s.jpg)
用python实现批量重命名文件的代码
2023-01-02 09:09:20
python实现去除空格及tab换行符的方法
2023-09-27 08:16:05
查询SQL Server Index上次Rebuild时间的方法
2024-01-14 11:31:23
python 对key为时间的dict排序方法
2021-03-22 17:09:59
Win10下Python3.7.3安装教程图解
2023-09-02 18:16:54
![](https://img.aspxhome.com/file/2023/6/62316_0s.jpg)
谈谈FACEBOOK的一处产品细节
2008-03-11 11:05:00
随滚动条移动的DIV层js代码
2007-10-10 12:51:00
解决python3 json数据包含中文的读写问题
2022-07-22 03:04:51
Python 网页请求之requests库的使用详解
2021-01-30 23:42:06
解决IIS出现Active Server Pages错误“ASP 0201”
2009-05-25 18:04:00
![](https://img.aspxhome.com/file/UploadPic/20093/9/200939182949746s.jpg)
windows下python模拟鼠标点击和键盘输示例
2021-11-12 21:06:32
用SQL统计SQLServe表存储空间大小的代码
2012-06-06 19:52:22
解决Python字典查找报Keyerror的问题
2021-05-07 00:53:55
![](https://img.aspxhome.com/file/2023/0/100800_0s.png)
python机器学习之神经网络(一)
2023-06-21 23:28:42
![](https://img.aspxhome.com/file/2023/9/130809_0s.jpg)
当设计师遭遇HTML5
2011-08-05 18:59:53
在for循环中length值是否需要缓存
2023-07-02 05:19:20
Django学习教程之静态文件的调用详解
2022-01-03 00:19:45
pycharm 使用心得(九)解决No Python interpreter selected的问题
2021-03-03 03:32:11
![](https://img.aspxhome.com/file/2023/6/76066_0s.png)
Python基于codecs模块实现文件读写案例解析
2023-08-31 16:25:21
![](https://img.aspxhome.com/file/2023/0/99450_0s.png)