mysql - having用法 - where having区别




在規範化數據庫中關於資源,主題和章節與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