我有两个表，我想减少比较两个表之间的记录所花费的时间。我无法将我的两个表内连接在一起，因为它们都是纵向表，这意味着动物标识可以重复几个，即使它是牛奶记录中唯一的
表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中的记录，这些记录是唯一的，唯一性是由动物和乳汁制成的
以下是我的子选择查询
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 |
+----+----------+---------+---------+------------+
最佳答案
也许这个
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)
关于mysql - 使用子选择联接两个表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/55647098/