我无法真正区分您想要实现的目标,但听起来您只是想获得一个显示每个章节及其主题和资源的表格。
如果是这样,则执行以下 SQL:
select * from resources r
JOIN topics_to_resource ttr ON ttr.tr_resid = r.res_id
JOIN topics t on t.t_id = ttr.tr_tid
JOIN topics_to_chapter ttc on ttc.tch_tid = t.t_id
JOIN chapters ch ON ch.ch_id = tch_chid
ORDER BY r.res_id;
将返回这样的结果,按照http://sqlfiddle.com/#!9/ddf252/12 http://sqlfiddle.com/#!9/ddf252/12
或者,忽略选择中的连接 ID:
select r.res_id, r.res_name, t.t_id, t.t_name, ch.ch_id, ch.ch_name from resources r
JOIN topics_to_resource ttr ON ttr.tr_resid = r.res_id
JOIN topics t on t.t_id = ttr.tr_tid
JOIN topics_to_chapter ttc on ttc.tch_tid = t.t_id
JOIN chapters ch ON ch.ch_id = tch_chid
ORDER BY r.res_id, t.t_id, ch.ch_id
as per http://sqlfiddle.com/#!9/ddf252/14 http://sqlfiddle.com/#!9/ddf252/14
如果这不是您想要的,您能否详细说明一下您想要看到的结果?
Edit:返回包含所有关联记录的更简洁的列表
select
CONCAT(r.res_id,': ',r.res_name) 'Resources',
GROUP_CONCAT(CONCAT(' (',t.t_id,': ',t.t_name,')')) 'Topics',
GROUP_CONCAT(CONCAT(' (',ch.ch_id,': ',ch.ch_name,')')) 'Chapters'
from resources r
JOIN topics_to_resource ttr ON ttr.tr_resid = r.res_id
JOIN topics t on t.t_id = ttr.tr_tid
JOIN topics_to_chapter ttc on ttc.tch_tid = t.t_id
JOIN chapters ch ON ch.ch_id = tch_chid
GROUP BY r.res_id
ORDER BY r.res_id, t.t_id, ch.ch_id
As per http://sqlfiddle.com/#!9/ddf252/30 http://sqlfiddle.com/#!9/ddf252/30
Finally,按章节和主题对它们进行分组:
select
CONCAT(res_id,': ',res_name) 'Resources',
GROUP_CONCAT(`chapters` order by chapters separator '\n') as 'Content'
FROM
(SELECT r.res_id 'res_id',
r.res_name 'res_name',
t.t_id 't_id',
t.t_name 't_name',
CONCAT(t.t_name,': (',GROUP_CONCAT(ch.ch_name ORDER BY t.t_name separator ','),')') 'Chapters'
FROM resources r
JOIN topics_to_resource ttr ON ttr.tr_resid = r.res_id
JOIN topics t on t.t_id = ttr.tr_tid
JOIN topics_to_chapter ttc on ttc.tch_tid = t.t_id
JOIN chapters ch ON ch.ch_id = tch_chid
GROUP BY res_id, t_id
ORDER BY r.res_id, t.t_id, ch.ch_id) as t
GROUP BY res_id
正如这里所见:http://sqlfiddle.com/#!9/ddf252/85 http://sqlfiddle.com/#!9/ddf252/85
我已经检查了结果,它们看起来不错 - 但请仔细检查,因为它有点像我脑海中的 MySQL Inception(这里已经是凌晨 1 点了)
进一步补充:每个资源都有不同的价值
select CONCAT(r.res_id,': ',r.res_name) 'Resources', GROUP_CONCAT(distinct t_name separator ',') 'Topics',
GROUP_CONCAT(distinct ch.ch_name separator ',') 'Chapters'
from resources r
JOIN topics_to_resource ttr ON ttr.tr_resid = r.res_id
JOIN topics t on t.t_id = ttr.tr_tid
JOIN topics_to_chapter ttc on ttc.tch_tid = t.t_id
JOIN chapters ch ON ch.ch_id = tch_chid
GROUP BY r.res_id
ORDER BY r.res_id, t.t_id, ch.ch_id
See http://sqlfiddle.com/#!9/ddf252/88 http://sqlfiddle.com/#!9/ddf252/88