group_concat - mysql with rollup




JOIN avec GROUP BY dans une base de données normalisée sur les ressources, les sujets et les chapitres (2)

C'est une syntaxe non standard:

SELECT *
...
GROUP BY RES.RES_ID

Ici, vous avez demandé pour chaque colonne (sélectionnez *) mais seulement spécifié une colonne sous le groupe par. SEULEMENT MySQL autorise cette syntaxe GROUP BY non standard qui est contrôlée par les paramètres du serveur. Ces paramètres par défaut ont récemment changé et vous pouvez constater que beaucoup de nos requêtes utilisant GROUP BY non standard pourraient échouer dans le futur.

Une clause GROUP BY conforme aux normes spécifie TOUTES les colonnes "non agrégées". Cela signifie que vous devez spécifier chaque colonne n'utilisant pas SUM / COUNT / AVG / MIN / MAX etc.

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

Cette requête a 2 formes de syntaxe de jointure. ** FROM t1, t2, t3 ** est ancien et imprudent et n'est PAS une bonne pratique. Aussi; ne jamais "combiner" cette ancienne syntaxe avec la syntaxe de jointure la plus récente dans une seule requête car cela peut conduire à des erreurs de requête.

N'utilisez PAS de virgules entre les tables dans la clause from, cette étape simple vous fera utiliser la meilleure syntaxe tout le temps.

En passant de SUJETS, CHAPITRES, RESSOURCES EN RES avec rien pour les limiter dans une clause where produira:

Multipliez chaque ligne de TOPICS par chaque ligne de CHAPTERS par chaque ligne de RESSOURCES. En d'autres termes, un "produit cartésien". Dans une syntaxe plus récente, votre requête se traduit par:

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

J'ai normalisé ma base de données mais je n'arrive pas à retourner les données que je recherche correctement.

J'ai 5 tables:

  1. Ressources (5 ressources)
  2. Sujets (10 sujets)
  3. Chapitres (10 chapitres)
  4. Topics-to-Resources (18 sujets à des liens de ressources)
  5. Rubriques à chapitres (18 liens de rubrique à chapitre)

Découvrez ce SQL Fiddle ...

Je dois rassembler tous les enregistrements dans la table Ressources et regrouper chacun d'entre eux avec leurs sujets et chapitres correspondants (des tables Topics-to-Resources et Topics-to-Chapters)

Quelqu'un peut-il suggérer la bonne requête SQL pour renvoyer les 5 enregistrements de ressources avec leurs sujets et chapitres?

J'ai essayé JOINS avec GROUP BY et cela condense le jeu d'enregistrements aux 5 ressources mais pas avec toutes les autres informations dont j'ai besoin (sujets et chapitres).

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

Je ne peux pas vraiment distinguer ce que vous essayez de réaliser, mais il semble que vous cherchez simplement à obtenir une table qui montre chaque chapitre avec son sujet et sa ressource.

Si oui, alors le SQL suivant:

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;

reviendra juste cela, selon http://sqlfiddle.com/#!9/ddf252/12

Ou, en ignorant les ID de jointure dans le select:

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

selon http://sqlfiddle.com/#!9/ddf252/14

Si ce n'est pas ce que vous cherchez, pourriez-vous élaborer un peu sur les résultats que vous cherchez à voir?

Modifier : pour renvoyer une liste plus concise avec tous les enregistrements associés

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

Selon http://sqlfiddle.com/#!9/ddf252/30

Enfin , pour regrouper ceux-ci par chapitre et par sujet:

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

Comme vu ici: http://sqlfiddle.com/#!9/ddf252/85

J'ai vérifié les résultats, et ils ont l'air bien - mais revérifiez, car il est allé un peu comme MySQL Inception dans ma tête (il est passé 1h ici)

Autre ajout: Valeurs distinctes par ressource

    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

Voir http://sqlfiddle.com/#!9/ddf252/88







normalization