我有 6 个表：3 个用于有关动漫/漫画/ova/任何内容的一般数据，3 个用于其类型，这是 1 到 m 的关系
anime: manga: ova:
aid | data | ... | mid | data | ... | oid | data | ... |
----+------+-----+ ----+------+-----+ ----+------+-----+
1 | .... | ... | 1 | .... | ... | 1 | .... | ... |
2 | .... | ... | 2 | .... | ... | 2 | .... | ... |
3 | .... | ... | 3 | .... | ... | 3 | .... | ... |
4 | .... | ... | 4 | .... | ... | 4 | .... | ... |
anime_genre: manga_genre: ova_genre:
aid | genre mid | name oid | genre
----+-------- ----+---------- ----+--------
1 | Ecchi 1 | Drama 1 | Action
1 | Action 2 | Ecchi 2 | Action
2 | Action 3 | Fighting 3 | Drama
3 | Drama 4 | Action 4 | Ecchi
4 | Action
每当有人搜索流派以在一个查询中获取所有信息时，我都会尝试对结果进行分组
案例1：
result on genre = Action:
genre | ids
--------+---------------------------------------------
Action | 1:anime 2:anime 4:anime 4:manga 1:ova 2:ova
result on genre = Ecchi:
genre | ids
--------+-------------------------------
Ecchi | 1:anime 4:anime 2:manga 4:ova
情况2(最好)：
result on genre = Action:
genre | id | common data | common data | common data
--------+---------+-------------+-------------+-------------
Action | 1:anime | ... | ... | ...
Action | 2:anime | ... | ... | ...
Action | 4:anime | ... | ... | ...
Action | 4:manga | ... | ... | ...
Action | 1:ova | ... | ... | ...
Action | 2:ova | ... | ... | ...
result on genre = Ecchi:
genre | id | common data | common data | common data
-------+---------+-------------+-------------+-------------
Ecchi | 1:anime | ... | ... | ...
Ecchi | 4:anime | ... | ... | ...
Ecchi | 2:manga | ... | ... | ...
Ecchi | 4:ova | ... | ... | ...
我整天都在砸头，我只能得到一张桌子的预期结果。我无法在一个查询中获取所有三个的结果。
有什么想法吗？有人可以指出我正确的方向吗？ (数据表有一些不常见的列，但我真的不在乎我是否得到这些的空值，因为我正在用 js 解析结果)
查找mysql join，您可以连接表并在多个表之间搜索匹配的答案。
您可以使用union all获取id的列表：
select ag.genre, 'anime' as which, ag.aid as id
from anime_genre ag
union all
select mg.genre, 'manga' as which, mg.mid as id
from manga_genre mg
union all
select og.genre, 'ova' as which, og.oid as id;
from ova_genre og;
可以在子查询中联接其他列。