萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 關聯查詢中 GROUP BY 分組聚合實戰:統計作品得票數

核心 SQL

SELECT 
    pr_rates.pid,
    COUNT(pr_rates.ip) AS vote_count,
    pr_works.title,
    pr_works.realname,
    pr_college.name
FROM pr_works
RIGHT JOIN pr_rates ON pr_rates.pid = pr_works.pid
LEFT JOIN pr_college ON pr_works.college = pr_college.id
GROUP BY pr_rates.pid
ORDER BY vote_count DESC;

關鍵點解析

  • GROUP BY pr_rates.pid:將相同 pid(作品 ID)的投票記錄合併為一組。
  • COUNT(pr_rates.ip):對每組內的 IP 計數,得到該作品得票數。
  • RIGHT JOIN pr_rates:以投票表為基準,確保無投票作品也不會被遺漏(若需顯示 0 票)。
  • LEFT JOIN pr_college:補全作品所屬學院名稱。
  • ORDER BY vote_count DESC:按得票數降冪排序,形成排行榜。

常見誤區與修正

❌ 非聚合欄位未出現在 GROUP BY 中

MySQL ONLY_FULL_GROUP_BY 模式下(5.7+ 預設開啟),SELECT 列表中的非聚合欄位(title、realname、name)必須出現在 GROUP BY 或包含在聚合函數中。上述 SQL 在嚴格模式下會報錯。

✅ 修正寫法(嚴格模式相容)

SELECT 
    r.pid,
    COUNT(r.ip) AS vote_count,
    MAX(w.title) AS title,
    MAX(w.realname) AS realname,
    MAX(c.name) AS college_name
FROM pr_rates r
LEFT JOIN pr_works w ON r.pid = w.pid
LEFT JOIN pr_college c ON w.college = c.id
GROUP BY r.pid
ORDER BY vote_count DESC;
  • 改用 LEFT JOIN 以投票表為主,語義更清晰。
  • 非聚合欄位用 MAX()/MIN() 包裹(同組內值相同),滿足 ONLY_FULL_GROUP_BY。

延伸:只查詢得票數 Top N

SELECT pid, vote_count
FROM (
    SELECT pid, COUNT(*) AS vote_count
    FROM pr_rates
    GROUP BY pid
) t
ORDER BY vote_count DESC
LIMIT 10;

子查詢先聚合,外層排序限制,效率更高,避免不必要的多表 JOIN。

copyright © 萬盛學電腦網 all rights reserved