使用哪些连接方法将多个表连接成一个视图

Question

我有一个MySQL数据库，其中包含多个表格，希望将这些表格联接成一个视图，以免我需要构建3或4个SQL语句，甚至是一个大的JOIN语句来获取相同的信息。

以下是我想要联接的所有表格

track_title               
+----+------------------+
| ID |      TITLE       |
+----+------------------+
|  1 | Title Here       |
|  2 | Another Title    |
|  3 | Some Other Title |
+----+------------------+

track_artist
+----+----------------+-----------+-----------+
| ID | TRACK_TITLE_ID | ARTIST_ID | SYMBOL_ID |
+----+----------------+-----------+-----------+
| 1  | 1              | 1         | 2         |
| 2  | 1              | 2         | 1         |
| 3  | 3              | 1         | 1         |
+----+----------------+-----------+-----------+

artist

+----+-------------+
| ID | ARTIST      |
+----+-------------+
| 1  | Linkin Park |
| 2  | Metallica   |
+----+-------------+

symbol
+----+--------+
| ID | SYMBOL |
+----+--------+
| 1  |        |
| 2  | Feat.  |
+----+--------+

tracklisting
+----+----------+----------+---------------+---------+
| ID | TRACK NO | TITLE_ID | VERSION       | DISC NO |
+----+----------+----------+---------------+---------+
| 1  | 1        | 1        |               | 1       |
| 2  | 1        | 2        | Album Version | 1       |
| 3  | 1        | 3        | Live Version  | 1       |
+----+----------+----------+---------------+---------+

这是我要寻找的最终视图。

+----+----------+------------------+---------------+-----------------------------+---------+
| ID | TRACK NO | TITLE            | VERSION       | ARTIST                      | DISC NO |
+----+----------+------------------+---------------+-----------------------------+---------+
| 1  | 1        | Title Here       |               | Linkin Park Feat. Metallica | 1       |
| 2  | 1        | Another Title    | Album Version |                             | 1       |
| 3  | 1        | Some Other Title | Live Version  | Linkin Park                 | 1       |
+----+----------+------------------+---------------+-----------------------------+---------+

我已经连续3天在左连接、右连接、内连接和外连接方面苦苦思索，但似乎一直无法让它正常工作。

基本上，我想要的是将track_artist表中的艺术家和符号从各自的表中获取，并将它们合并到一个列中。然后，将标题和合并列连接起来以得到这个视图。

full_artist_view
+----------+------------------+-----------------------------+
| TITLE_ID | TITLE            | FULL_ARTIST                 |
+----------+------------------+-----------------------------+
| 1        | Title Here       | Linkin Park Feat. Metallica |
| 2        | Another Title    |                             |
| 3        | Some Other Title | Linkin Park                 |
+----------+------------------+-----------------------------+

我已经做到了这一步，但是当我尝试将其与曲目列表表连接时，我的服务器似乎崩溃了，这让我非常痛苦。没有mysql错误，所以我猜我使用了错误的连接，或者这根本不可能。（虽然我看不出为什么不可能）

曲目列表表每周都会舒适地增长1000条记录，并且当前已有大约75000条记录。

对我来说，这应该可以工作的SQL，但实际上却不行。

FROM full_artist_view LEFT JOIN tracklisting ON 
full_artist_view.TITLE_ID = tracklisting.TITLE_ID

- Mark

---

你能发布一下 full_artist_view 的代码吗？ - Taryn

Answer 1

虽然我只有你的一个小数据样本，看不到你的full_artist_view代码长什么样子。但你应该能够通过以下方法获得结果：

select tt.id,
  tl.`track no`,
  tt.title,
  coalesce(tl.version, '') version,
  group_concat(concat(coalesce(a.artist, ''), ' ', coalesce(s.symbol, '')) order by a.artist SEPARATOR ' ') artist,
  tl.`disc no`
from track_title tt
inner join tracklisting tl
  on tt.id = tl.TITLE_ID
left join track_artist ta
  on tt.id = ta.TRACK_TITLE_ID
left join artist a
  on ta.artist_id = a.id
left join symbol s
  on ta.symbol_id = s.id
group by tt.id, tl.`track no`, tt.title, tl.version, tl.`disc no`

请查看带演示的SQL Fiddle。这将返回：

| ID | TRACK NO |            TITLE |       VERSION |                       ARTIST | DISC NO |
---------------------------------------------------------------------------------------------
|  1 |        1 |       Title Here |               | Linkin Park Feat. Metallica  |       1 |
|  2 |        1 |    Another Title | Album Version |                              |       1 |
|  3 |        1 | Some Other Title |  Live Version |                 Linkin Park  |       1 |

- Taryn

---

这个例子中，少量记录的情况下运行得很完美，比如这里。但是当记录集变得非常大时，例如70000条以上，就会崩溃。我认为连接操作在处理大型记录集时变得过于复杂。除非我漏掉了什么。 - Mark

@Mark，你的表上有哪些索引？执行计划显示了什么？ - Taryn

我可能忘记在我的列上放置索引。一旦我添加了索引，它就完美地工作了。感谢所有的帮助。 - Mark

@Mark，我很高兴你解决了问题！很高兴能帮到你。 :) - Taryn

Answer 2

快速播放，我认为（但不确定）你需要的是这样的东西：

SELECT a.Id, a.Track_No, b.Title, a.Version, GROUP_CONCAT(CONCAT(d.Artists, e.Symbol)), a.Disc_No
FROM tracklisting a
INNER JOIN track_title b ON a.Title_id = b.Id
LEFT OUTER JOIN track_artist c ON b.Id = c.Track_Title_Id
LEFT OUTER JOIN artist d ON c.Artist_Id = d.Id
LEFT OUTER JOIN symbol e ON d.SymbolId = e.Id
GROUP BY a.Id, a.Track_No, b.Title, a.Version, a.Disc_No

但是我可能漏掉了一些东西，所以请告诉我！

- Kickstart