MySQL 多表关联一对多查询实现取最新一条数据的方法示例
本文实例讲述了MySQL多表关联一对多查询实现取最新一条数据的方法。分享给大家供大家参考，具体如下：
MySQL多表关联一对多查询取最新的一条数据
遇到的问题
多表关联一对多查询取最新的一条数据，数据出现重复
由于历史原因，表结构设计不合理；产品告诉我说需要导出客户信息数据，需要导出客户的所属行业，纳税性质数据；但是这两个字段却在订单表里面，每次客户下单都会要求客户填写；由此可知，客户数据和订单数据是一对多的关系；那这样的话，问题就来了，我到底以订单中的哪一条数据为准呢？经过协商后一致同意以最新的一条数据为准；
数据测试初始化SQL脚本
DROPTABLEIFEXISTS`customer`;
CREATETABLE`customer`(
`id`BIGINTNOTNULLCOMMENT'客户ID',
`real_name`VARCHAR(20)NOTNULLCOMMENT'客户名字',
`create_time`DATETIMENOTNULLCOMMENT'创建时间',
PRIMARYKEY(`id`)
)ENGINE=INNODBDEFAULTCHARSET=UTF8COMMENT'客户信息表';
--DATAFORTABLEcustomer
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7717194510959685632','张三','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7718605481599623168','李四','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7720804666226278400','王五','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7720882041353961472','刘六','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7722233303626055680','宝宝','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7722233895811448832','小宝','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7722234507982700544','大宝','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7722234927631204352','二宝','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7722235550724423680','小贱','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7722235921488314368','小明','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7722238233975881728','小黑','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7722246644138409984','小红','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7722318634321346560','阿狗','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7722318674321346586','阿娇','2019-01-2316:23:05');
INSERTINTO`demo`.`customer`(`id`,`real_name`,`create_time`)VALUES('7722318974421546780','阿猫','2019-01-2316:23:05');
DROPTABLEIFEXISTS`order_info`;
CREATETABLE`order_info`(
`id`BIGINTNOTNULLCOMMENT'订单ID',
`industry`VARCHAR(255)DEFAULTNULLCOMMENT'所属行业',
`nature_tax`VARCHAR(255)DEFAULTNULLCOMMENT'纳税性质',
`customer_id`VARCHAR(20)NOTNULLCOMMENT'客户ID',
`create_time`DATETIMENOTNULLCOMMENT'创建时间',
PRIMARYKEY(`id`)
)ENGINE=INNODBDEFAULTCHARSET=UTF8COMMENT'订单信息表';
--DATAFORTABLEorder_info
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7700163609453207552','餐饮酒店类','小规模','7717194510959685632','2019-01-2316:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7700163609453207553','餐饮酒店类','小规模','7717194510959685632','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7700167995646615552','高新技术','一般纳税人','7718605481599623168','2019-01-2316:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7700167995646615553','商贸','一般纳税人','7718605481599623168','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7700193633216569344','商贸','一般纳税人','7720804666226278400','2019-01-2316:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7700193633216569345','高新技术','一般纳税人','7720804666226278400','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7700197875671179264','餐饮酒店类','一般纳税人','7720882041353961472','2019-01-2316:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7700197875671179266','餐饮酒店类','一般纳税人','7720882041353961472','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7703053372673171456','高新技术','小规模','7722233303626055680','2019-01-2316:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7703053372673171457','高新技术','小规模','7722233303626055680','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709742385262698496','服务类','一般纳税人','7722233895811448832','2019-01-2316:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709742385262698498','服务类','一般纳税人','7722233895811448832','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745055683780608','高新技术','小规模','7722234507982700544','2019-01-2316:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745055683780609','进出口','小规模','7722234507982700544','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745249439653888','文化体育','一般纳税人','7722234927631204352','2019-01-2416:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745249439653889','高新技术','一般纳税人','7722234927631204352','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745453266051072','高新技术','小规模','7722235550724423680','2019-01-2416:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745453266051073','文化体育','小规模','7722235550724423680','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745539848413184','科技','一般纳税人','7722235921488314368','2019-01-2416:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745539848413185','高新技术','一般纳税人','7722235921488314368','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745652603887616','高新技术','一般纳税人','7722238233975881728','2019-01-2416:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745652603887617','科技','一般纳税人','7722238233975881728','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745755528568832','进出口','一般纳税人','7722246644138409984','2019-01-2416:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745755528568833','教育咨询','小规模','7722246644138409984','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745892539047936','教育咨询','一般纳税人','7722318634321346560','2019-01-2416:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709745892539047937','进出口','一般纳税人','7722318634321346560','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709746000127139840','生产类','小规模','7722318674321346586','2019-01-2416:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709746000127139841','农业','一般纳税人','7722318674321346586','2019-01-2317:09:53');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709746447445467136','农业','一般纳税人','7722318974421546780','2019-01-2416:54:25');
INSERTINTO`demo`.`order_info`(`id`,`industry`,`nature_tax`,`customer_id`,`create_time`)VALUES('7709746447445467137','生产类','小规模','7722318974421546780','2019-01-2317:09:53');
- 按需求写的SQL语句：
UPDATEorder_infoSETcreate_time=NOW();
- 尝试解决问题
SELECT cr.id, cr.real_name, oi.industry, oi.nature_tax FROM customerAScr LEFTJOIN( SELECTa.industry,a.nature_tax,a.customer_id,a.create_timeFROMorder_infoASa LEFTJOIN( SELECTMAX(create_time)AScreate_time,customer_idFROMorder_infoGROUPBYcustomer_id )ASbONa.customer_id=b.customer_id WHEREa.create_time=b.create_time )ASoiONoi.customer_id=cr.id GROUPBYcr.id;
数据重复嘛，小意思，加个GROUPBY不就解决了吗？我怎么会这么机智，哈哈哈！！！但是当我执行完SQL的那一瞬间，我又懵逼了，查询出来的结果中所属行业，纳税性质仍然不是最新的；看来是我想太多了，还是老老实实的解决问题吧。。。
- 找出重复数据
SELECT cr.id, cr.real_name, oi.industry, oi.nature_tax FROM customerAScr LEFTJOIN( SELECTa.industry,a.nature_tax,a.customer_id,a.create_timeFROMorder_infoASa LEFTJOIN( SELECTMAX(create_time)AScreate_time,customer_idFROMorder_infoGROUPBYcustomer_id )ASbONa.customer_id=b.customer_id WHEREa.create_time=b.create_time )ASoiONoi.customer_id=cr.id GROUPBYcr.idHAVINGCOUNT(cr.id)>=2;
- 执行结果如下：
SELECT cr.id, cr.real_name, oi.industry, oi.nature_tax FROM customerAScr LEFTJOIN( SELECTa.industry,a.nature_tax,a.customer_id,a.create_timeFROMorder_infoASa LEFTJOIN( SELECTMAX(id)ASid,customer_idFROMorder_infoGROUPBYcustomer_id )ASbONa.customer_id=b.customer_id WHEREa.id=b.id )ASoiONoi.customer_id=cr.id;
哎，终于解决了。。。
更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》、《MySQL数据库锁相关技巧汇总》及《MySQL常用函数大汇总》
希望本文所述对大家MySQL数据库计有所帮助。