在有关资源，主题和章节的规范化数据库中与GROUP BY一起加入

小编典典

在有关资源，主题和章节的规范化数据库中与GROUP BY一起加入

sql

我已经规范化了数据库，但似乎无法以正确的方式返回我要查找的数据。

我有5张桌子：

资源（5资源）
主题（10个主题）
章节（10章）
主题到资源（18个主题到资源的链接）
主题到章节（18个主题到章节的链接）

看看这个SQL小提琴…

我需要收集“资源”表中的所有记录，并将每个记录与相应的主题和章节分组（来自“主题到资源”和“主题到章节”表）

谁能提出正确的SQL查询以返回5个资源记录及其参加的主题和章节？

我已经尝试过使用GROUP BY进行JOINS，这会将记录集浓缩为5种资源，但没有提供我需要的所有其他信息（主题和章节）。

SELECT * FROM TOPICS, CHAPTERS, RESOURCES AS RES
INNER JOIN TOPICS_to_RESOURCE AS TR ON RES.RES_ID = TR.TR_RESID
INNER JOIN TOPICS_to_CHAPTER AS TCH ON TR.TR_TID = TCH.TCH_TID
GROUP BY RES.RES_ID

阅读 166

2021-04-15

共1个答案

小编典典

我无法真正区分您要实现的目标，但是听起来您只是在寻找一个表，该表显示每章及其主题和资源。

如果是这样，则下面的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返回

或者，忽略选择中的联接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

按照 http://sqlfiddle.com/#!9/ddf252/14

如果这不是您想要的，您能否详细说明一下您希望看到的结果？

编辑：要返回具有所有相关记录的更简洁的列表

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

按照 http://sqlfiddle.com/#!9/ddf252/30

最后，按章节和主题对它们进行分组：

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

我已经检查了结果，看起来还不错-但请仔细检查，因为有点像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

2021-04-15