好湿?好紧?好多水好爽自慰,久久久噜久噜久久综合,成人做爰A片免费看黄冈,机机对机机30分钟无遮挡

主頁 > 知識庫 > MySQL 多表關聯一對多查詢實現取最新一條數據的方法示例

MySQL 多表關聯一對多查詢實現取最新一條數據的方法示例
熱門標簽：鄭州網絡外呼系統價錢 上海市三維地圖標注 怎么更改高德地圖標注 電話機器人是電腦呼號嗎 云南大數據外呼系統 博樂電銷機器人 400電話到哪辦理優惠 機器人打電銷電話 南寧外呼系統招商

本文實例講述了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相關內容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》、《MySQL數據庫鎖相關技巧匯總》及《MySQL常用函數大匯總》

希望本文所述對大家MySQL數據庫計有所幫助。

您可能感興趣的文章:
  • MyBatisPlus 一對多、多對一、多對多的完美解決方案
  • 解決mybatis plus 一對多分頁查詢問題
  • MyBatis一次執行多條SQL語句的操作
  • 解決mybatis一對多關聯查詢多條數據只顯示一條的問題

標簽：杭州 寧夏 恩施 益陽 白銀 定西 澳門 秦皇島

巨人網絡通訊聲明：本文標題《MySQL 多表關聯一對多查詢實現取最新一條數據的方法示例》，本文關鍵詞  MySQL,多表,關聯,一對多,；如發現本文內容存在版權問題，煩請提供相關信息告之我們，我們將及時溝通與處理。本站內容系統采集于網絡，涉及言論、版權與本站無關。
  • 相關文章
  • 下面列出與本文章《MySQL 多表關聯一對多查詢實現取最新一條數據的方法示例》相關的同類信息！
  • 本頁收集關于MySQL 多表關聯一對多查詢實現取最新一條數據的方法示例的相關信息資訊供網民參考！
    • 推薦文章
    主站蜘蛛池模板： 黄站无毒不卡秒播网站免费观看| 无翼乌邪恶彩色无摭挡的图片| 啊灬嗯灬别揉我的奶了啊灬| 爽死你个放荡粗暴小淫货男女视频| 顶流夫妇今天秀恩爱了吗小不点爱吃肉 | 无码h片在线观看网站无禁| 久久国产精品无码一级毛片| 成年人网站入口| 免费看秘?美女隐私图片| 韩国电影《教室爱欲》| 自拍一区在线观看| 男女边摸边吃奶边做免费视频| 一线视频在线免费观看| 苗族免费一级A片在线| 性欧美极品videos| 91香蕉国产在线观看人员| 艳母在线视频| 男女啪啪免费软件下载| 两根粗大同进同出| 又黄又爽又污又舒服又刺激| freesexvide| 农村顶住岳的肥臀| 脔到她乖h糙汉1v1| 国产一级爱| 热久久国产欧美一区二区精品| 双性暗卫被皇上玩到哭H| 福利一区福利二区微拍刺激 | 久久久久久精品免费观看| 女人裸乳图片400张| 久久精品天天爽夜夜爽| 天天综合干| 啪啪tv| 91精品人妻一区二区50路| 男女啪啪做爰高潮全过有多重打法 | 波多野结衣一二三区| 一本一道AV无码中文蜜桃视频| 绝色美妇 迎合娇吟| 欧美黑人xxxxx性受| 破处喷水| 97伦理片| 欧美激情电影片在线观看|