您好,登錄后才能下訂單哦!
這篇文章主要用實例解析MySQL多表關(guān)聯(lián)一對多查詢?nèi)绾螌崿F(xiàn)取最新一條數(shù)據(jù),內(nèi)容簡而易懂,希望大家可以學(xué)習(xí)一下,學(xué)習(xí)完之后肯定會有收獲的,下面讓小編帶大家一起來看看吧。
MySQL 多表關(guān)聯(lián)一對多查詢?nèi)∽钚碌囊粭l數(shù)據(jù)
由于歷史原因,表結(jié)構(gòu)設(shè)計不合理;產(chǎn)品告訴我說需要導(dǎo)出客戶信息數(shù)據(jù),需要導(dǎo)出客戶的 所屬行業(yè),納稅性質(zhì) 數(shù)據(jù);但是這兩個字段卻在訂單表里面,每次客戶下單都會要求客戶填寫;由此可知,客戶數(shù)據(jù)和訂單數(shù)據(jù)是一對多的關(guān)系;那這樣的話,問題就來了,我到底以訂單中的哪一條數(shù)據(jù)為準(zhǔn)呢?經(jīng)過協(xié)商后一致同意以最新的一條數(shù)據(jù)為準(zhǔn);
數(shù)據(jù)測試初始化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 '創(chuàng)建時間', 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 '所屬行業(yè)', `nature_tax` VARCHAR(255) DEFAULT NULL COMMENT '納稅性質(zhì)', `customer_id` VARCHAR(20) NOT NULL COMMENT '客戶ID', `create_time` DATETIME NOT NULL COMMENT '創(chuàng)建時間', 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', '餐飲酒店類', '小規(guī)模', '7717194510959685632', '2019-01-23 16:54:25'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7700163609453207553', '餐飲酒店類', '小規(guī)模', '7717194510959685632', '2019-01-23 17:09:53'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7700167995646615552', '高新技術(shù)', '一般納稅人', '7718605481599623168', '2019-01-23 16:54:25'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7700167995646615553', '商貿(mào)', '一般納稅人', '7718605481599623168', '2019-01-23 17:09:53'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7700193633216569344', '商貿(mào)', '一般納稅人', '7720804666226278400', '2019-01-23 16:54:25'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7700193633216569345', '高新技術(shù)', '一般納稅人', '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', '高新技術(shù)', '小規(guī)模', '7722233303626055680', '2019-01-23 16:54:25'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7703053372673171457', '高新技術(shù)', '小規(guī)模', '7722233303626055680', '2019-01-23 17:09:53'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7709742385262698496', '服務(wù)類', '一般納稅人', '7722233895811448832', '2019-01-23 16:54:25'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7709742385262698498', '服務(wù)類', '一般納稅人', '7722233895811448832', '2019-01-23 17:09:53'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7709745055683780608', '高新技術(shù)', '小規(guī)模', '7722234507982700544', '2019-01-23 16:54:25'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7709745055683780609', '進出口', '小規(guī)模', '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', '高新技術(shù)', '一般納稅人', '7722234927631204352', '2019-01-23 17:09:53'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7709745453266051072', '高新技術(shù)', '小規(guī)模', '7722235550724423680', '2019-01-24 16:54:25'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7709745453266051073', '文化體育', '小規(guī)模', '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', '高新技術(shù)', '一般納稅人', '7722235921488314368', '2019-01-23 17:09:53'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7709745652603887616', '高新技術(shù)', '一般納稅人', '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', '教育咨詢', '小規(guī)模', '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', '生產(chǎn)類', '小規(guī)模', '7722318674321346586', '2019-01-24 16:54:25'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7709746000127139841', '農(nóng)業(yè)', '一般納稅人', '7722318674321346586', '2019-01-23 17:09:53'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7709746447445467136', '農(nóng)業(yè)', '一般納稅人', '7722318974421546780', '2019-01-24 16:54:25'); INSERT INTO `demo`.`order_info` (`id`, `industry`, `nature_tax`, `customer_id`, `create_time`) VALUES ('7709746447445467137', '生產(chǎn)類', '小規(guī)模', '7722318974421546780', '2019-01-23 17:09:53');
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;
數(shù)據(jù)重復(fù)嘛,小意思,加個 GROUP BY 不就解決了嗎?我怎么會這么機智,哈哈哈!?。〉钱?dāng)我執(zhí)行完SQL的那一瞬間,我又懵逼了,查詢出來的結(jié)果中 所屬行業(yè),納稅性質(zhì) 仍然不是最新的;看來是我想太多了,還是老老實實的解決問題吧。。。
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;
哎,終于解決了。。。
以上就是關(guān)于用實例解析MySQL多表關(guān)聯(lián)一對多查詢?nèi)绾螌崿F(xiàn)取最新一條數(shù)據(jù)的內(nèi)容,如果你們有學(xué)習(xí)到知識或者技能,可以把它分享出去讓更多的人看到。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。