mysql - SQL 选择父项作为列名，子项作为值

Question

我正在创建一个数据库来存储音乐。

有不同的类别，每个类别都有子类别。示例:

id    name                  parentID
1     instrumentation       null
2     solo_instrument       null
3     Concert Band          1
4     Brass Band            1
5     Fanfare Band          1
6     Clarinet              2
7     Saxophone             2
8     Trumpet               2

另一方面，我有一个表存储链接到 categoryID 的 musicID

id    categoryID  musicID
1     4           1
2     8           1
3     3           2
4     6           2

我需要查询的以下结果:

musicID    instrumentation    solo_instrument
1          Brass Band         Trumpet
2          Concert Band       Clarinet

我被告知要使用树结构，因为将来很可能会添加其他类别，这应该能够支持它。但是，我无法弄清楚如何编写查询来获得上述结果。

当我首先选择乐器，然后选择 solo_instrument 时，我得到了我想要的结果，但这都是硬编码的，并且不允许音乐轨道只有一个 parentID，因为我单独选择它们。

这甚至可能吗？或者我应该彻底检查我的数据库结构吗？我想看看你的建议。

Answer 1

您应该能够使用条件聚合来解决这个问题。

查询:

SELECT
    mu.musicID,
    MAX(CASE WHEN cp.name = 'instrumentation' THEN ca.name END) instrumentation,
    MAX(CASE WHEN cp.name = 'solo_instrument' THEN ca.name END) solo_instrument
FROM
    musics mu
    INNER JOIN categories ca ON ca.id = mu.categoryID
    INNER JOIN categories cp ON cp.id = ca.parentID
GROUP by mu.musicID

INNER JOIN拉起对应的类别，再上一层找到父类别。如果创建了新的根类别，您只需向查询中添加更多 MAX() 列。

在 this DB Fiddle demo 使用您的示例数据，查询返回:

| musicID | instrumentation | solo_instrument |
| ------- | --------------- | --------------- |
| 1       | Brass Band      | Trumpet         |
| 2       | Concert Band    | Clarinet        |