时间戳

首页 > 解决方案 > 使用子选择连接两个表

mysql - 使用子选择连接两个表

问题描述

我有两个表，我想减少比较两个表之间的记录所需的时间。我无法内部加入我的两张表，因为它们都是纵向表，这意味着即使它是牛奶记录所独有的，也可以复制多个动物

表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 |
+----+----------+---------+---------+------------+

标签： mysql

解决方案

或许这

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)

推荐阅读