分享：SQL中 LEFT JOIN 左表合并去重实用技巧

建表：

CREATE TABLE `table1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) DEFAULT NULL,
  `age` varchar(200) DEFAULT NULL,
  `sponsor_id` varchar(20) DEFAULT NULL COMMENT '业务发起人',
  `gmt_create_user` int(11) NOT NULL COMMENT '创建人id',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` datetime DEFAULT NULL COMMENT '修改时间',
  `gmt_modified_user` int(11) DEFAULT NULL COMMENT '修改人id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COMMENT='测试表1';

CREATE TABLE `table2` (
  `kid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) DEFAULT NULL,
  `sponsor_id` varchar(20) DEFAULT NULL COMMENT '业务发起人',
  `type` int(11) NOT NULL COMMENT '创建人id',
  `gmt_create` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `gmt_modified` datetime DEFAULT NULL COMMENT '修改时间',
  `gmt_modified_user` int(11) DEFAULT NULL COMMENT '修改人id',
  PRIMARY KEY (`kid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COMMENT='测试表2';

插入数据：

`INSERT INTO `table1`(`id`, `name`, `age`, `sponsor_id`, `gmt_create_user`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (1, 't1', '11', '10', 1, '2018-10-10 20:34:03', NULL, NULL);
INSERT INTO `table1`(`id`, `name`, `age`, `sponsor_id`, `gmt_create_user`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (2, 't2', '12', '10', 2, '2018-10-10 20:34:03', NULL, NULL);
INSERT INTO `table1`(`id`, `name`, `age`, `sponsor_id`, `gmt_create_user`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (3, 't3', '13', '10', 3, '2018-10-10 20:34:03', NULL, NULL);
INSERT INTO `table1`(`id`, `name`, `age`, `sponsor_id`, `gmt_create_user`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (4, 't4', '14', '20', 4, '2018-10-10 20:34:03', NULL, NULL);

`INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (1, 't1', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (2, 't2', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (3, 't3', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (4, 't4', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (5, 't5', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (6, 't6', '10', 1, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (7, 't7', '10', 2, '2018-10-10 20:38:10', NULL, NULL);
INSERT INTO `table2`(`kid`, `name`, `sponsor_id`, `type`, `gmt_create`, `gmt_modified`, `gmt_modified_user`) VALUES (8, 't1', '11', 1, '2018-10-10 20:38:10', NULL, NULL);

查询异常：

`SELECT
 a.*,
 b.type 
FROM
 table1 a
 LEFT JOIN table2 b ON a.sponsor_id = b.sponsor_id 
WHERE
 b.type = 1 
 AND a.sponsor_id = 10;

简单说明问题出现的原因：

MySQL left join 语句格式为：A LEFT JOIN B ON 条件表达式

left join 是以A表为基础，A表即左表，B表即右表。

左表(A)的记录会全部显示，而右表(B)只会显示符合条件表达式的记录，如果在右表(B)中没有符合条件的记录，则记录不足的地方为NULL。

使用left join， A表与B表所显示的记录数为 1:1 或 1:0，A表的所有记录都会显示，B表只显示符合条件的记录。

但如果B表符合条件的记录数大于1条，就会出现1:n的情况，这样left join后的结果，记录数会多于A表的记录数。

所以解决办法 都是从一个出发点出发，使A表与B表所显示的记录数为 1:1对应关系。

解决方法：

使用非唯一标识的字段做关联

1. DISTINCT

   select DISTINCT(id) from a left join b on a.id=b.aid 

   DISTINCT查询结果是 第一个表唯一的数据 重复的结果没显示出来

   SELECT
   DISTINCT(a.id), a.*,
   b.type 
   FROM
   table1 a
   LEFT JOIN table2 b ON a.sponsor_id = b.sponsor_id 
   WHERE
   b.type = 1 
   AND a.sponsor_id = 10;

   SELECT
   DISTINCT a.*,
   b.type 
   FROM
   table1 a
   LEFT JOIN table2 b ON a.sponsor_id = b.sponsor_id 
   WHERE
   b.type = 1 
   AND a.sponsor_id = 10;

2. GROUP BY

   select * from a left join(select id from b group by id) as b on a.id=b.aid

   拿出b表的一条数据关联 使A表与B表所显示的记录数为 1:1对应关系。

   SELECT 
   a.*,
   b.type 
   FROM
   table1 a
   LEFT JOIN ( SELECT * FROM table2 GROUP BY sponsor_id ) AS b ON a.sponsor_id = b.sponsor_id 
   WHERE
   b.type = 1 
   AND a.sponsor_id = 10;

3. max取唯一

   select * from a left join (select max(id) from table group by id) as b on a.id=b.aid 

   拿出b表的最后一条数据关联

   SELECT
   a.*,
   b.type 
   FROM
   table1 a
   LEFT JOIN ( SELECT MAX( kid ), type, sponsor_id FROM table2 GROUP BY sponsor_id ) AS b ON a.sponsor_id = b.sponsor_id 
   WHERE
   b.type = 1 
   AND a.sponsor_id = 10;

4. IN巧用

   SELECT
   a.* 
   FROM
   table1 a 
   WHERE
   a.sponsor_id IN ( SELECT sponsor_id FROM table2 WHERE type = 1 AND sponsor_id = 10 );

   SELECT
   a.*,
   1 
   FROM
   table1 a 
   WHERE
   a.sponsor_id IN ( SELECT sponsor_id FROM table2 WHERE type = 1 AND sponsor_id = 10 );

   

相信对于熟悉SQL的人来说，LEFT JOIN非常简单，采用的时候也很多，但是有个问题还是需要注意一下。假如一个主表M有多个从表的话A B C …..的话，并且每个表都有筛选条件，那么把筛选条件放到哪里，就得注意喽。

比如有个主表M，卡号是主键。

有个从表A，客户号、联系方式是联合主键，其中联系方式，1-座机，2-手机号码

如果想要查询所有卡号对应的手机号码两个字段，很简单，SQL语句如下：

SELECT A.卡号,B.手机号码
FROM A
LEFT JOIN B
  ON A.客户号=B.客户号
WHERE B.联系方式='2'

相信很多人这样写，估计实际工作中也会看到这样的语句，并不是说这么写一定会错误，实际SQL表达的思想一定是要符合业务逻辑的。

前面已经说清楚，所有卡号对应的手机号码。所有卡号，所以首先肯定以A表作为主表，并且左关联B表，这样A表所有的卡号一定会显示出来，但是如果B表的筛选条件放到最外层，这样就相当于将A表关联B表又做了一遍筛选，结果就是

就会筛选出来这么一条数据，丢失了A表中其他的卡号。

实际工作中表结构肯定没这么简单，关联的表也会很多，当有很多条件时，最好这么写

SELECT A.卡号,B.手机号码
FROM A
LEFT JOIN (
    SELECT * FROM B
    B.联系方式='2'
    )B
  ON A.客户号=B.客户号

这么写的话，A表中的数据肯定会完全保留，又能与B表的匹配，不会丢失数据。

本作品采用《CC 协议》，转载必须注明作者和本文链接