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