1对多只取⼀条mysql_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');
citizenship翻译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');
powerful strength line reducingINSERT 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();vieworks
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
ate_time = b.create_time
) AS oi ON oi.customer_id = cr.id
GROUP BY cr.id;
数据重复嘛，⼩意思，加个 GROUP BY 不就解决了吗？我怎么会这么机智，哈哈哈但是当我执⾏完SQL的那⼀瞬间，我⼜懵逼了，查询出来的结果中 所属⾏业，纳税性质 仍然不是最新的；看来是我想太多了，还是⽼⽼实实的解决问题吧。。。
出重复数据
SELECT
mysql语句多表查询>indefinitelycr.id,
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
ate_time = b.create_time
) AS oi ON oi.customer_id = cr.id
GROUP BY cr.id HAVING COUNT(cr.id) >= 2;
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数据库计有所帮助。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论