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:按得票數降冪排序,形成排行榜。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。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。