我在mysql中有一个带有多个表的db,希望将多个表连接到一个视图中,这样就不必构建3或4个sql语句,甚至不用在php中构建一个大型连接语句来获取相同的信息。
这是我所有想加入的桌子
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 |
+----+----------+------------------+---------------+-----------------------------+---------+
在过去的三天里,我一直在用左键、右键、连接键和完全连接键猛击自己的头,但似乎无法让它工作。
基本上,我想要发生的是track_artist表将艺术家和符号从各自的表中提取出来,并将它们连接到一个列中。然后连接title和concat列以获得此视图。
full_artist_view
+----------+------------------+-----------------------------+
| TITLE_ID | TITLE | FULL_ARTIST |
+----------+------------------+-----------------------------+
| 1 | Title Here | Linkin Park Feat. Metallica |
| 2 | Another Title | |
| 3 | Some Other Title | Linkin Park |
+----------+------------------+-----------------------------+
我已经走了这么远,但当我尝试加入到tracklisting表时,我似乎崩溃了我的服务器,这变得非常痛苦。没有mysql错误,所以我猜我使用了错误的连接,或者这是不可能的。
tracklisting表每周都以1000张的舒适度持续增长,目前的记录数为+75000张。
对我来说,这是SQL应该可以工作,但不能
FROM full_artist_view LEFT JOIN tracklisting ON
full_artist_view.TITLE_ID = tracklisting.TITLE_ID
最佳答案
虽然我只有你的小数据样本,我看不到你的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 with Demo。这将返回:
| 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 |