SQL：计数加入不起作用

I have an SQL query where I simply join two tables. One table contain comments and the other is the user table. I join the tables to in a simple manner get user information about the user who wrote the comment (username) at the same time get the comment (comment text etc.).

我有一个SQL查询,我只需要连接两个表。一个表包含注释,另一个表是用户表。我以简单的方式加入表格,获取有关写评论(用户名)的用户同时获取评论(评论文本等)的用户信息。

Now I want to count the number of comments to write the correct number of comments on the top of the page. I do this by adding a COUNT, and an alias to save the value.

现在我想计算注释的数量,以在页面顶部写出正确数量的注释。我这样做是通过添加COUNT和别名来保存值。

When I echo numCount, I get the correct value of comments, but I get no comments in my comment loop. As soon as I remove the count, I get all comments again. What am I doing wrong?

当我回复numCount时,我得到正确的评论值,但我的评论循环中没有任何评论。一旦我删除了计数,我就会再次收到所有评论。我究竟做错了什么?

SELECT
ncID, ncText, ncDate,
uID, uName, uImageThumb,
COUNT(a.ncID) AS numComments
FROM tblNewsComments a LEFT JOIN tblUsers b
ON a.ncUserID = b.uID
WHERE  a.ncNewsID = $newID 
ORDER BY ncDate DESC

4 个解决方案

#1

I am going to assume this is MySQL (or maybe SQLite), since most other RDBMS would fail on this query. The issue is that you are missing a GROUP BY clause, which is required when using an aggregate function like COUNT() unless it is to operate over the entire rowset. MySQL's unusual behavior is to allow the absence of a GROUP BY, or to allow columns in SELECT which are not also in the GROUP BY, producing unusual results.

我将假设这是MySQL(或者可能是SQLite),因为大多数其他RDBMS会在此查询中失败。问题是您缺少GROUP BY子句,这在使用COUNT()等聚合函数时是必需的,除非它在整个行集上运行。 MySQL的异常行为是允许缺少GROUP BY,或者允许SELECT中不在GROUP BY中的列产生异常结果。

The appropriate way to do this would be to join in a subquery which returns the COUNT() per ncID.

执行此操作的适当方法是加入子查询,该子查询返回每个ncID的COUNT()。

SELECT
  ncID,
  ncText,
  ncDate,
  uID,
  uName,
  uImageThumb,
  /* The count returned by the subquery */
  ccount.numComments
FROM
  tblNewsComments a
  LEFT JOIN tblUsers b ON a.ncUserID = b.uID
  /* Derived table returns only ncID and count of comments */
  LEFT JOIN (
    SELECT ncID, COUNT(*) AS numComments 
    FROM tblNewsComments 
    GROUP BY ncID
  ) ccount ON a.ncID = ccount.ncID
WHERE a.ncNewsID = $newID
ORDER BY ncDate DESC

Edit Whoops - looks like you wanted the count per ncID, not the count per ncUserID as I originally had it.

编辑哎呀 - 看起来你想要每个ncID的计数,而不是我最初拥有它的每个ncUserID的计数。

#2

I don't know what SQL engine you are using, but what you have here is not valid SQL and should be flagged as such.

我不知道你正在使用什么SQL引擎,但你在这里所拥有的不是有效的SQL,应该被标记为这样。

COUNT is an aggregate function and you can only apply those to groups or a whole table, so in your case you would probably do

COUNT是一个聚合函数,你只能将它们应用于组或整个表,所以在你的情况下你可能会这样做

SELECT
ncID, ncText, ncDate,
uID, uName, uImageThumb,
COUNT(a.ncID) AS numComments
FROM tblNewsComments a LEFT JOIN tblUsers b
ON a.ncUserID = b.uID
WHERE  a.ncNewsID = $newID 
GROUP BY ncID, ncText, ncDate,
uID, uName, uImageThumb
ORDER BY ncDate DESC

#3

You're using an AGGREGATE function (Count) but you're needing a GROUP BY to make any sense from that count.

您正在使用AGGREGATE函数(Count),但是您需要GROUP BY才能从该计数中获得任何意义。

I suggest adding "GROUP BY [all other field names except the COUNT]" to your query

我建议在查询中添加“GROUP BY [除COUNT以外的所有其他字段名称]”

#4

Try this:

SELECT  
ncID, ncText, ncDate,  
uID, uName, uImageThumb,  
(SELECT COUNT(ncID)   
FROM   
tblNewsComments a  
INNER JOIN  
tblUsers b  
ON a.ncUserID = b.uID)   
AS numComments  
FROM tblNewsComments a LEFT JOIN tblUsers b  
ON a.ncUserID = b.uID  
WHERE  a.ncNewsID = $newID   
ORDER BY ncDate DESC

#1