mysql - LEFT JOIN with COUNT 出现错误

Question

我想计算一个 left join mysql 的条目，但我总是得到一个错误

SELECT g.uid,g.datei, u.id, u.avatar, u.avatarapproved, a.points, COUNT (g.uid) as hits
FROM jos_findme_gallery as g 
LEFT JOIN jos_findme as u ON g.uid= u.user_id 
LEFT JOIN jos_alpha_userpoints as a ON g.uid= a.userid 
WHERE u.avatar IS NOT NULL
ORDER BY g.uid asc

FUNCTION db1140411-dualda.COUNT does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

有什么想法吗？

Answer 1

您应该在使用聚合函数时使用GROUP BY，但由于COUNT 之间的空格，您会收到此特定错误和 (。

From the MySQL Docs:

The requirement that function calls be written with no whitespace between the name and the parenthesis applies only to the built-in functions that have special considerations. COUNT is one such name.

要解决这个问题，去掉空格...并加入一个 GROUP BY 来显式:

SELECT 
  g.uid,
  g.datei, 
  u.id, 
  u.avatar, 
  u.avatarapproved, 
  a.points, 
  COUNT(g.uid) as hits
FROM jos_findme_gallery as g 
LEFT JOIN jos_findme as u ON g.uid= u.user_id 
LEFT JOIN jos_alpha_userpoints as a ON g.uid= a.userid 
WHERE u.avatar IS NOT NULL
GROUP BY 
  g.uid,
  g.datei, 
  u.id, 
  u.avatar, 
  u.avatarapproved, 
  a.points
ORDER BY g.uid asc