mysql - 使用 subselect 连接两个表

Question

我有两个表，我想减少比较两个表之间的记录所花费的时间。我无法内部连接我的两个表，因为它们都是纵向表，这意味着 animalid 可以重复多个，即使它对于 milk 记录是唯一的

表1(我的错误表)

+-----+----------+---------+---------+------------+
| #ID | animalid | milkmor | milkeve |  milkdate  |
+-----+----------+---------+---------+------------+
|   1 | animal_A |      12 |      12 | 2019-09-12 |
|   2 | animal_A |      12 |      13 | 2018-09-12 |
+-----+----------+---------+---------+------------+

表2(我的准确记录表)

+-----+----------+---------+---------+------------+
| #ID | animalid | milkmor | milkeve |  milkdate  |
+-----+----------+---------+---------+------------+
|   1 | animal_A |      12 |      12 | 2017-09-12 |
|   2 | animal_A |      12 |      13 | 2018-09-12 |
+-----+----------+---------+---------+------------+

我想比较两个表，只显示表 1 中唯一的记录，唯一性由 animalid 和 milkdate 组成

下面是我的子选择查询

SELECT * FROM table1, table2
WHERE  table1.animalid = table2.animalid AND table1.milkdate <> table2.milkdate

预期输出:

+----+----------+---------+---------+------------+
| ID | animalid | milkmor | milkeve |  milkdate  |
+----+----------+---------+---------+------------+
|  1 | animal_A |      12 |      12 | 2019-09-12 |
+----+----------+---------+---------+------------+

Answer 1

也许是这样

drop table if exists t,t2;
create table t (ID  int, animalid varchar(20), milkmor int, milkeve int,  milkdate  date);
create table t2 (ID  int, animalid varchar(20), milkmor int, milkeve int,  milkdate  date);
insert into t values
(   1 , 'animal_A' ,      12 ,      12 , '2019-09-12'),
(   2 , 'animal_A' ,      12 ,      13 , '2018-09-12');
insert into t2 values
(   1 , 'animal_A' ,      12 ,      12 , '2017-09-12'), 
(   2 , 'animal_A' ,      12 ,      13 , '2018-09-12'); 

select distinct t.*
from t
where not exists (select 1 from t2 where t2.animalid = t.animalid and t2.milkdate = t.milkdate);    

+------+----------+---------+---------+------------+
| ID   | animalid | milkmor | milkeve | milkdate   |
+------+----------+---------+---------+------------+
|    1 | animal_A |      12 |      12 | 2019-09-12 |
+------+----------+---------+---------+------------+
1 row in set (0.00 sec)