mysql - 如何在 MySQL 中使用子查询呈现两个表的混合结果？

Question

可能题名不完全正确。我会尝试更好地解释我的问题。我有两个具有以下结构的表:

表 01:

ID | GENUS | SPECIES | INDIVIDUUM
 1 |     A |       a | alfa
 2 |     B |       b | beta
 3 |     C |       c | gama
 4 |     D |       d | delta
 5 |     E |       e | epsilon

表 02:

ID1 | ID2 | INDEX
  1 |   2 |   21%
  1 |   3 |   17%
  1 |   4 |   32%
  1 |   5 |   43%
  2 |   1 |   21%
  2 |   3 |   19%
  2 |   4 |   94%
  2 |   5 |   91%
  .     .       .
  .     .       .
  .     .       .
  5 |   1 |   43%
  5 |   2 |   91%
  5 |   3 |   83%
  5 |   4 |   76%

比我做以下选择:

SELECT id FROM table01 WHERE individuum in (alfa,epsilon);

并收到以下结果:

| ID |
|  1 |
|  5 |

我做了另一个选择:

SELECT * FROM table02 WHERE ID1 in (1,5);

正如您可能已经知道的那样，这次我收到以下结果:

结果 02:

ID1 | ID2 | INDEX
  1 |   2 |   21%
  1 |   3 |   17%
  1 |   4 |   32%
  1 |   5 |   43%
  5 |   1 |   43%
  5 |   2 |   91%
  5 |   3 |   83%
  5 |   4 |   76%

现在我想根据这个结果创建一个新表，它会给我以下字段(和值):

id | individuum | species | genus | id2 | individuum2 | species2 | genus2 | index |
 1 |       alfa |       a |     A |   2 |        beta |        b |      B |   21% |
 1 |       alfa |       a |     A |   3 |        gama |        c |      C |   17% |
 1 |       alfa |       a |     A |   4 |       delta |        d |      D |   32% |
 1 |       alfa |       a |     A |   5 |     epsilon |        e |      E |   43% |
 5 |    epsilon |       e |     E |   1 |        alfa |        a |      A |   43% |
 5 |    epsilon |       e |     E |   2 |        beta |        b |      B |   91% |
 5 |    epsilon |       e |     E |   3 |        gama |        c |      C |   83% |
 5 |    epsilon |       e |     E |   4 |       delta |        d |      D |   76% |

它以某种“分析”方式呈现第一个表中的数据，就像 RESULT02 以“综合”方式向我们展示的那样。

我虽然有类似下面的内容，但我不确定它是否正确。

SELECT
b.id1,
(SELECT a.individuum FROM table01 a WHERE id = a.id1) individuum,
(SELECT a.species FROM table01 a WHERE id = a.id1) species,
(SELECT a.genus FROM table01 a WHERE id = a.id1) genus,
b.id2,
(SELECT a.individuum FROM table01 a WHERE id = a.id2) individuum2,
(SELECT a.species FROM table01 a WHERE id = a.id2) species2,
(SELECT a.genus FROM table01 a WHERE id = a.id2) genus2,
b.index
FROM
    table02 b
WHERE
    individuum in (alfa , epsilon);
ORDER BY index DESC";

如果您能帮助我创建更好、更快、更高效的查询，我将不胜感激。

Answer 1

尝试看看这是否有帮助:

SELECT
  table01.id,
  table01.individuum,
  table01.species,
  table01.genus,
  -- -----------------
  table02.id as id2,
  -- -----------------
  t01.individuum as individuum2
  t01.species as species2, 
  t01.genus as genus2,
  -- -----------------
  table02.index
from
  table01
inner join
  table02 on table01.ID = table02.ID1
left join
  table01 as t01 on t01.ID = table02.ID2
where
    table01.individuum in (alfa , epsilon)
order by
  index DESC;