mysql - 在规范化数据库中关于资源,主题和章节与GROUP BY联接




join group-by (2)

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

我有5个表格:

  1. 资源(5资源)
  2. 话题(10话题)
  3. 章节(10章)
  4. 主题到资源(资源链接的18个主题)
  5. 主题到章节(18章到章节链接)

看看这个SQL小提琴 ...

我需要收集“资源”表中的所有记录,并将其中的每个记录与相应的主题和章节(从主题到资源和主题到章节表)

任何人都可以建议正确的SQL查询返回5参考主题和章节的资源记录?

我已经尝试与GROUP BY连接,这将记录集压缩到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

我无法真正区分你想要达到的目标,但是听起来你只是想要获得一张表格来显示每一章的主题和资源。

如果是这样,那么下面的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返回

或者,忽略select中的联接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

请参阅http://sqlfiddle.com/#!9/ddf252/88


这是非标准的语法:

SELECT *
...
GROUP BY RES.RES_ID

在这里你要求每一列(select *),但只在group by下指定一列。 只有MySQL允许这个由服务器设置控制的非标准的GROUP BY语法。 这些默认设置最近已更改,您可能会发现许多使用非标准GROUP BY的查询将来可能会失败。

符合标准的GROUP BY子句指定所有“非聚合”列。 含义你必须指定每一列不使用 SUM / COUNT / AVG / MIN / MAX等

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

该查询有2种形式的连接语法。 **从t1,t2,t3 **是古老而不明智的,不是很好的做法。 也; 从来没有将这个旧的语法形式与单个查询中最近的连接语法“结合起来”,因为这会导致查询错误。

只是不要在from子句中的表之间使用逗号,这个简单的步骤将使您一直使用更好的语法。

顺便说一下, 从“主题”,“章节”,“资源作为解决方案”中可以得出:

在CHAPTER中的每一行乘以TOPICS中的每一行,将其中的每一行乘以RESOURCES中的每一行。 换句话说就是“笛卡尔产品”。 在更新的语法中,您的查询转换为:

SELECT * FROM TOPICS
CROSS JOIN CHAPTERS
CROSS JOIN 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




normalization