mysql - 如何对mysql进行连接查询

Question

我有 2 个表，一个(我们称之为“轨道”)将包含音乐轨道信息，例如标题、时间、样本文件、价格和所有需要的东西。第二个表(我们称之为“类别”)将包含第一个表中的轨道 ID、类别类型(乐器、流派、情绪等)和类别 ID。

特定轨道的类别数量可能未知。例如，id=1 的轨道可以有乐器类别 3 和 4，情绪类别 1、2、3 等等。

我需要获取可排序(按标题、时间、价格或其他)的轨道列表，这些轨道还使用 LIMIT(用于分页)按心情或乐器或任何类型过滤。

谁能帮我组装这样的查询？我不知道如何开始。

表结构示例:

表格“轨道”

+----+---------+-------+------+
| id |  title  | Price | Time |
+----+---------+-------+------+
|  1 | Title 1 |    10 |    0 |
|  2 | Title 2 |     9 |    1 |
|  3 | Title 3 |     8 |    2 |
+----+---------+-------+------+

表“类别”

    +----+------------+-------------+----------+
    | id |    type    | category_id | track_id |
    +----+------------+-------------+----------+
    |  1 | instrument |           1 |        1 |
    |  2 | instrument |          10 |        1 |
    |  3 | genre      |           1 |        1 |
    |  4 | mood       |          15 |        2 |
    +----+------------+-------------+----------+

我当然会使用 int 作为类别类型，但我使用 words 作为示例。

更新致命缺陷:

juergen d 解决方案运行良好，但它有一个致命缺陷。如果我在单独的下拉列表中有所有可能的乐器、流派和情绪，并且我想通过选择下拉框来过滤掉具有乐器 1、情绪 2 和流派 3 的轨道。该列表显示所有具有乐器 1 或心情 2 或...不是 AND 的轨道。

谁能帮我修改一下？

Answer 1

更好的数据库设计应该是

表格“轨道”

+----+---------+-------+------+
| id |  title  | Price | Time |
+----+---------+-------+------+
|  1 | Title 1 |    10 |    0 |
|  2 | Title 2 |     9 |    1 |
|  3 | Title 3 |     8 |    2 |
+----+---------+-------+------+

表“类别”

+----+------------+
| id |    type    |
+----+------------+
|  1 | instrument |
|  2 | genre      |
|  3 | mood       |
+----+------------+

表“track_categories”

+-------------+-------------------------+
| category_id | sub_category | track_id | 
+-------------+-------------------------+
|           1 |           3  |        1 |
|           1 |           1  |        1 |
|           3 |           1  |        1 |
|           2 |           3  |        2 |
+-------------+-------------------------+

然后您可以运行查询(例如流派轨道)

select t.*
from tracks t
join track_categories tc on tc.track_id = t.id
join categories c on c.id = tc.category_id
where c.type = 'genre'

如果您想选择具有特定类型组合的轨道，请使用

select t.title
from tracks t
join track_categories tc on tc.track_id = t.id
join categories c on c.id = tc.category_id
group by t.title
having sum(c.type = 'instrument' and tc.sub_category = 1) > 0
and sum(c.type = 'mood' and tc.sub_category = 2) > 0
and sum(c.type = 'genre' and tc.sub_category = 3) > 0