mysql - 寻找最有效的选择查询

Question

我有一个表，需要不同行的结果，其中一列中必须存在不同的键值。

我每天从另一个系统通过 REST API 获取此表一次，然后像这样(简化)查看我的 MySQL 数据库:

+----+------+------------+-------+
| ID | type | date       | value |
+----+------+------------+-------+
|  1 | A    | 2019-06-01 |     1 |
|  2 | B    | 2019-06-01 |     2 |
|  3 | A    | 2019-06-02 |     4 |
|  4 | B    | 2019-06-03 |     5 |
|  9 | A    | 2019-06-09 |    11 |
| 10 | B    | 2019-06-09 |    14 |
| 11 | A    | 2019-06-24 |    99 |
+----+------+------------+-------+

现在，我需要一个选择，它只在同一日期存在类型 A 和类型 B 的值时产生所有结果。结果应该是这样的:

+------------+-------+--------+----+------+------------+-------+
| date       | typeA | valueA | ID | type | date       | value |
+------------+-------+--------+----+------+------------+-------+
| 2019-06-01 | A     |      1 |  2 | B    | 2019-06-01 |     2 |
| 2019-06-09 | A     |     11 | 10 | B    | 2019-06-09 |    14 |
+------------+-------+--------+----+------+------------+-------+

• 第一个想法:

使用子选择...对于大表来说可能不是个好主意...但我不确定。

SELECT * from 
(SELECT date as dateA, value as valueA from V1 where type = 'A') AS subA,
(SELECT date as dateB, value as valueB from V1 where type = 'B') AS subB
WHERE dateA = dateB

• 第二个想法:

创建两个临时表然后进行连接。但是需要时间来创建两个表并连接两个新的临时表。

CREATE TEMPORARY table tA SELECT date, type as typeA, value as valueA from V1 WHERE type = 'A';
CREATE TEMPORARY table tB SELECT date, type as typeB, value as valueB from V1 WHERE type = 'B';

SELECT * from tA 
INNER JOIN tB on tA.date = tB.date;

• 第三个想法:

仅在临时表上创建并将其用于使用主表的连接:

CREATE TEMPORARY table tB SELECT * from V1 WHERE type = 'B';

SELECT * from V1 
INNER JOIN tB on V1.date = tB.date
where V1.type = 'A'

我的哪个想法最适合大 table ，或者有更好的解决方案。

提前致谢。

Answer 1

你需要一个自内连接

select select v1.date,v1.type as typeA, v1.value as valueA, 
       v2.id, v2.type as typeB, v2.date, v2.value
  from v v1 join v v2    -- v is table name
    on v1.date = v2.date
    and v1.type = 'A' and v2.type = 'B';

+------------+-------+--------+----+------+------------+-------+
| date       | typeA | valueA | ID | type | date       | value |
+------------+-------+--------+----+------+------------+-------+
| 2019-06-01 | A     |      1 |  2 | B    | 2019-06-01 |     2 |
| 2019-06-09 | A     |     11 | 10 | B    | 2019-06-09 |    14 |
+------------+-------+--------+----+------+------------+-------+

Demo