时间戳

首页 > 解决方案 > Mysql：仅使用最新记录加入 2 个表

mysql - Mysql：仅使用最新记录加入 2 个表

问题描述

我有 2 个表，tableA 和 tableB 看起来像这样：

表A：

在此处输入图像描述

表B：

在此处输入图像描述

我设法通过使用 tableA 和 tableB 的以下 sql 通过自动递增 id 获取最新记录：

SELECT *
FROM tableA 
WHERE id IN (SELECT MAX(id) AS id
             FROM tableA 
             GROUP BY social_num) 


SELECT *
FROM tableB 
WHERE id IN (SELECT MAX(id) AS id
             FROM tableB 
             GROUP BY social_num)

如何将它们连接在一起，以便通过 social_num 获得 tableA 上的最新记录和 tableB 链接上的最新记录？我尝试了以下sql，它可以工作：

SELECT *
FROM tableA 
LEFT JOIN tableB ON tableA.social_num = tableB.social_num
WHERE tableA.id IN (SELECT MAX(id) AS id
             FROM tableA 
             GROUP BY social_num) 
AND
tableB.id IN (SELECT MAX(id) AS id
             FROM tableB 
             GROUP BY social_num)

但是我如何将 tableA 中的所有其他用户也一起显示，例如Morris Q

DDL 信息

CREATE TABLE `tableA` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(255) DEFAULT NULL,
  `social_num` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

/*Data for the table `tableA` */

insert  into `tableA`(`id`,`fullname`,`social_num`,`email`) values (1,'David J','1155','davidj@gmail.com'),(2,'Brian H','2244','brianh@gmail.com'),(3,'Hawkins M','6677','hawkins@gmail.com'),(4,'Marry K','7122','marry@gmail.com'),(5,'Utah O','9123','utah@gmail.com'),(6,'James L','1266','james@gmail.com'),(7,'David J','1155','david@hotmail.com'),(8,'Marry K','7122','marry@gmail.com'),(9,'Johnson E','1180','johnson@gmail.com'),(10,'Hawkins M','6677','hawkins@yahoo.com'),(11,'Morris Q','1461','morris@gmail.com'),(12,'David J','1155','david@yahoo.com');

CREATE TABLE `tableB` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fullname` varchar(255) DEFAULT NULL,
  `social_num` varchar(255) DEFAULT NULL,
  `phone_num` varchar(255) DEFAULT NULL,
  `fav_color` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

/*Data for the table `tableB` */

insert  into `tableB`(`id`,`fullname`,`social_num`,`phone_num`,`fav_color`) values (1,'Brian H','2244','912-112-1231','blue'),(2,'Johnson E','1180','912-221-5512','red'),(3,'David J','1155','812-231-6125','green'),(4,'Utah O','9123','741-661-3125','red'),(5,'Hawkins M','6677','881-331-6612','blue'),(6,'James L','1266','934-513-5132','yellow'),(7,'Brian H','2244','785-513-9821','green'),(8,'David J','1155','960-231-5151','black'),(9,'Hawkins M','6677','135-661-3516','red');

任何帮助都会很棒。谢谢

标签： mysql

解决方案

由DBA的 Kondybas 回答

SELECT a.*, b.*
  FROM 
       (SELECT MAX(w.id) AS id
             , w.social_num
          FROM tableA AS w
         GROUP BY w.social_num
       ) AS maxa
  LEFT JOIN 
       (SELECT MAX(z.id) AS id
             , z.social_num
          FROM tableB AS z
         GROUP BY z.social_num
       ) AS maxb  ON maxb.social_num = maxa.social_num
  JOIN tableA       AS a  ON a.id = maxa.id
  LEFT JOIN tableB  AS b  ON b.id = maxb.id
;

推荐阅读