时间戳

首页 > 解决方案 > 如何将mysql子查询转移到左连接？

mysql - 如何将mysql子查询转移到左连接？

问题描述

如何将子查询转换为左连接？

SELECT * FROM  member  
WHERE shop_id = 3215138 AND grade = 2  
AND nick NOT IN (
    SELECT `buyer_nick` FROM item 
    WHERE shop_id=3215138 AND num_iid IN (23696088262, 522953128382)
);

表结构如下：

mysql> DESCRIBE member

+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| member_id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| shop_id            | int(11)      | NO   | MUL | NULL    |                |
| nick               | varchar(255) | NO   |     |         |                |
| name               | varchar(255) | NO   |     |         |                |
| mobile             | varchar(255) | NO   |     |         |                |
| grade              | int(11)      | NO   |     | -1      |                |
| created            | int(11)      | NO   |     |         |                |
| modified           | int(11)      | NO   |     |         |                |
+--------------------+--------------+------+-----+---------+----------------+


    CREATE TABLE `member` (
  `member_id` int(11) NOT NULL AUTO_INCREMENT,
  `shop_id` int(11) NOT NULL,
  `nick` varchar(255) NOT NULL DEFAULT '',
  `grade` int(11) NOT NULL DEFAULT '-1',
  `created` int(11) NOT NULL,
  PRIMARY KEY (`member_id`),
  UNIQUE KEY `shop_id` (`shop_id`,`nick`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8


mysql> DESCRIBE item;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| shop_id    | int(11)      | NO   | MUL | NULL    |                |
| buyer_nick | varchar(255) | NO   |     |         |                |
| num_iid    | bigint(11)   | NO   |     | NULL    |                |
| num        | int(11)      | NO   |     | 0       |                |
| times      | int(11)      | NO   |     | 0       |                |
| created    | int(11)      | NO   |     | 0       |                |
| modified   | int(11)      | NO   |     | 0       |                |
+------------+--------------+------+-----+---------+----------------+

CREATE TABLE `item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `shop_id` int(11) NOT NULL,
  `buyer_nick` varchar(255) NOT NULL DEFAULT '',
  `num_iid` bigint(11) NOT NULL COMMENT 'Tobao ItemID',
  `num` int(11) NOT NULL DEFAULT '0',
  `created` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniqk` (`shop_id`,`buyer_nick`,`num_iid`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

注意：拥有查询 num_iid 的 item.buyer_nick 也可以拥有另一个 num_iid；我试过了：

SELECT * FROM  member  AS m 
    LEFT JOIN item AS i 
    ON m.nick=i.buyer_nick
    WHERE m.shop_id = 3215138 AND i.shop_id=3215138 AND m.grade=2 
    AND i.num_iid NOT IN (23696088262, 522953128382)

并且结果仍然拥有拥有num_iid(23696088262, 522953128382)和其他num_iid的buyer_nick，不等于子查询结果；

示例数据：

表成员数据：

| member_id | shop_id | nick |grade |  created    |
| 1         |  3215138|  A   |   2  |   1540968764|
| 2         |  3215138|  B   |   2  |   1540968764|
| 3         |  3215138|  C   |   2  |   1540968764|

表项数据：

|id| shop_id|  buyer_nick|  num_iid    |num|  created  |
|1 | 3215138|  A         | 23696088262 | 3 |1540968764 | 
|2 | 3215138|  A         | 522953128382| 2 |1540968764 | 
|3 | 3215138|  A         | 123456789   | 2 |1540968764 |

子查询结果为 nick B, C row (NOT INCLUDE A)

标签： mysql

解决方案

您可以在下面尝试使用左连接

  • 从表结构来看，这两个表之间的关系键是member.nick and buyer_nick

  • 左连接并将所有其他条件放在 on 子句中会为您提供所有行，但是当您buyer_nick is null在 where
    子句中定义条件时，它将为您提供项目
    表中不存在的那些昵称

使用

SELECT * FROM  member left join item on member.nick= item.`buyer_nick`
and member.shop_id = 3215138 AND member.grade = 2  
where item.`buyer_nick` is null

推荐阅读