mysql - 对表缺少唯一行或主键的表使用NOT IN()mysql

我有两个表：AP和表AP supervisory visits，分别具有一对多关系。 AP supervisory visits没有主键或唯一索引（因此使用起来有些困难），并且函数“ IN（）”使用起来并不轻松。

表AP：

+------------------------------+-------------+------+-----+---------+-------+
| Field                        | Type        | Null | Key | Default | Extra |
+------------------------------+-------------+------+-----+---------+-------+
| HID                          | varchar(50) | NO   | PRI | NULL    |       |
| yr                           | varchar(50) | NO   | PRI | NULL    |       |
| mo                           | varchar(50) | NO   | PRI | NULL    |       |

表AP supervisory visits：

+------------------+-------------+------+-----+---------+-------+
| Field            | Type        | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------+-------+
| HID              | varchar(50) | YES  | MUL | NULL    |       |
| yr               | varchar(50) | YES  |     | NULL    |       |
| mo               | varchar(50) | YES  |     | NULL    |       |
| exported         | datetime    | YES  |     | NULL    |       |
| visitor name     | varchar(50) | YES  |     | NULL    |       |
| title            | varchar(50) | YES  |     | NULL    |       |
| reason for visit | varchar(50) | YES  |     | NULL    |       |
| number of visits | int(11)     | YES  | MUL | 0       |       |
+------------------+-------------+------+-----+---------+-------+

我在AP supervisory visits中有大约8000条AP记录和1700条唯一记录（使用HID，yr，mo）。我希望AP表中的所有记录在AP supervisory visits表中都没有“子级”。使用有关Stakeoverflow的其他文章，我得出的结论是我应该使用“ NOT IN（）”。这适用于我的一些具有相似关系的表，但是这次不行（请注意，最后一个查询是反向查询，它缺少NOT）：

mysql> SELECT HID, yr, mo FROM AP
    -> WHERE (HID, yr, mo) NOT IN(SELECT HID, yr, mo FROM `AP supervisory visits`);
Empty set (0.34 sec)

mysql> SELECT HID, yr, mo FROM AP
    -> WHERE (HID, yr, mo) NOT IN(SELECT DISTINCT HID, yr, mo FROM `AP supervisory visits`);
Empty set (0.47 sec)

mysql> SELECT HID, yr, mo FROM AP
    -> WHERE (HID, yr, mo) NOT IN(SELECT DISTINCT * FROM (SELECT DISTINCT HID, yr, mo FROM `AP supervisory visits`) AS temp);
Empty set (3.04 sec)

mysql> SELECT HID, yr, mo FROM AP
    -> WHERE (HID, yr, mo) IN(SELECT DISTINCT HID, yr, mo FROM `AP supervisory visits`) LIMIT 5;
+-----+------+----+
| HID | yr   | mo |
+-----+------+----+
| 109 | 2011 | 03 |
| 109 | 2012 | 05 |
| 109 | 2012 | 06 |
| 110 | 2010 | 11 |
| 110 | 2010 | 12 |
+-----+------+----+
5 rows in set (0.00 sec)

从那以后，我已经创建了一个临时表，其中包含HID，yr，mo的所有不同组合，但是我宁愿完全不使用临时表。上面的第三个查询应该是在内存中用不同的值创建一个临时表（我知道这很丑），但事实并非如此。

我的“临时解决方案”：

mysql> CREATE TABLE myTempAPSup SELECT DISTINCT HID, yr, mo FROM `AP supervisory visits`;
mysql> ALTER TABLE myTempAPSup ADD PRIMARY KEY(HID, yr, mo);
mysql> SELECT HID, yr, mo FROM AP
    -> WHERE (HID, yr, mo) NOT IN (SELECT HID, yr, mo FROM myTempAPSup) LIMIT 5;
+-----+------+----+
| HID | yr   | mo |
+-----+------+----+
| 109 | 2010 | 01 |
| 109 | 2012 | 01 |
| 109 | 2012 | 02 |
| 109 | 2012 | 03 |
| 109 | 2012 | 04 |
+-----+------+----+
5 rows in set (0.00 sec)

有没有办法从如此糟糕的表中获取没有“孩子”的行？我以为我的麻烦源于缺少唯一/主键，但是我是否还缺少其他语法上的“陷阱”？

最佳答案

如果您来自Oracle背景，那么左联接然后检查null的想法似乎很奇怪。但是，该解决方案速度很快，因为您无需扫描联接表中的每条记录。另一种方法是对不存在子句使用相关子查询。

SELECT a.HID
     , a.yr
     , a.mo
  FROM AP a
 WHERE NOT EXISTS (
       SELECT v.HID
         FROM `AP supervisory visits` v
        WHERE v.HID = a.HID
          AND v.yr  = a.yr
          AND v.mo  = a.mo
 )

关于mysql - 对表缺少唯一行或主键的表使用NOT IN()mysql，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/24253991/

ap

mysql - 对表缺少唯一行或主键的表使用NOT IN()mysql