mysql - 我如何将两个具有不同内部连接的咨询放在一起

Question

我有这个表:

表格主题

+---------+-------------+
| tema_id | nombre      |
+---------+-------------+
|       1 | Aprender    |
|       4 | Justicia    |
|       2 | Pensamiento |
|       3 | Vida        |
+---------+-------------+

TABLE frase(作者的短语)

+----+----------+----------------------------------------+
| id | autor_id | texto                                  |
+----+----------+----------------------------------------+
|  1 |        2 | Pienso, luego existo                   |
|  2 |        1 | Aprender sin pensar es trabajo perdido |
+----+----------+----------------------------------------+

TABLE tema_frase(与table frase和tema的关系)

+----------+---------+
| frase_id | tema_id |
+----------+---------+
|        1 |       2 |
|        2 |       1 |
|        2 |       2 |
+----------+---------+

TABLE author(词组的作者)

+----+-----------+----------------------------------------+
| id | nombre    | descripcion                            |
+----+-----------+----------------------------------------+
|  1 | Confucio  | Pensador chino                         |
|  2 | Descartes | Filósofo y matemático francés       |
+----+-----------+----------------------------------------+

我想展示的方式:表格

+------------+------------------+----------------------------+
| autor      | phrases          | themes                     |
+------------+------------------+----------------------------+
| Confucio   | Pensador chino   | Pensamiento                |
| Descartes  | Filósofo y      | Pensamiento, aprender      |
+------------+------------------+----------------------------+

我知道显示带有主题的短语和带有作者的短语，但我不知道如何将它们组合在一起，因为我需要放入“FROM”两个表，但由于 INNERS JOINS 而我不能:

咨询短语和主题:

SELECT frase.texto as frase, group_concat(tema.nombre separator ", ") as temas FROM tema_frase INNER JOIN tema ON tema_frase.tema_id=tema.tema_id INNER JOIN frase ON frase.id=tema_frase.frase_id 组通过 frase.texto

咨询短语和作者:

SELECT autor.nombre 作为 Autor，frase.texto 作为 Frases FROM frase INNER JOIN autor ON autor.id=frase.autor_id

Answer 1

您可以将 autor 加入到第一个查询中，并将作者添加到 group by 中:

SELECT 
  autor.nombre as Autor, 
  frase.texto as frase, 
  group_concat(tema.nombre separator ", ") as temas 
FROM tema_frase 
INNER JOIN tema ON tema_frase.tema_id=tema.tema_id 
INNER JOIN frase ON frase.id=tema_frase.frase_id 
INNER JOIN autor ON autor.id=frase.autor_id
GROUP BY autor.nombre, frase.texto