MySQL Join Query错误，我做错了什么？

I have 3 tables:

我有3张桌子：

users
用户
items
项目
user_groups

user_groups
Each user can post multiple items to items table

每个用户都可以将多个项目发布到项目表
Each user has one record in user_groups which defines what user group he belongs to (e.g new users, junior users, senior users admins, etc)
每个用户在user_groups中都有一条记录，用于定义他所属的用户组（例如新用户，初级用户，高级用户管理员等）

I'm trying to get a list of the ids of the users who meet the following criteria:

我正在尝试获取符合以下条件的用户的ID列表：

Belong to the user group '3'
属于用户组'3'
Have posted less than 5 items today
今天发布的商品少于5件

Here's my query:

这是我的查询：

SELECT u.id, COUNT (i.id) as numPosted
FROM users u, items i
    JOIN user_groups on user_groups.userId = u.id
    AND user_groups.groupId = '5'
 WHERE                          
     i.userId = u.id AND
     i.datePosted = CURDATE() AND                         
     numPosted < 5

In the JOIN, its making sure the user belongs to the user group 5
在JOIN中，它确保用户属于用户组5
And in the Where its checking that the items belong to the userId, and the items where posted today, and the number of items posted was less than 5.
并且在其检查项目属于userId的位置，以及今天发布的项目以及发布的项目数量小于5。

When I run this query, I get this error:

当我运行此查询时，我收到此错误：

#1630 - FUNCTION items.COUNT does not exist. Check the 
'Function Name Parsing and Resolution' section in the Reference Manual

What am I doing wrong?

我究竟做错了什么？

2 个解决方案

#1

Remove the space after COUNT, setting groupId = '3' (assuming groupId really is text), and adding a HAVING clause:

删除COUNT后的空格，设置groupId ='3'（假设groupId确实是文本），并添加HAVING子句：

SELECT u.id, COUNT(i.id) as numPosted
  FROM users u
       JOIN user_groups g ON (g.userId = u.id AND g.groupId = '3')
       LEFT OUTER JOIN items i ON (i.userId = u.id and i.datePosted = CURDATE())
  GROUP BY u.id
  HAVING COUNT(i.id) < 5

There is also the question of capturing users who made no posts. Straight join and you will lose those users, an outer join will be needed in there.

还有一个问题是捕获没有发帖的用户。直接加入，你将失去这些用户，那里将需要外部联接。

#2

First of all, CURDATE() returns the current date with an implicit time of 00:00:00. So if you say : CURDATE() == "2012-09-16 00:38:16" it will return 0 because CURDATE is "2012-09-16 00:00:00". You should use a BETWEEN statement: datePosted BEWEEN CURDATE() and NOW() (which implicitly indicates BETWEEN '2012-09-16 00:00:00' AND '2012-09-16 00:38:16').

首先，CURDATE（）返回当前日期，隐含时间为00:00:00。因此，如果您说：CURDATE（）==“2012-09-16 00:38:16”它将返回0，因为CURDATE是“2012-09-16 00:00:00”。您应该使用BETWEEN语句：datePosted BEWEEN CURDATE（）和NOW（）（隐式指示BETWEEN'2012-09-16 00:00:00'和'2012-09-16 00:38:16'）。

You may try :

你可以尝试：

SELECT u.id, COUNT(i.id) as numPosted
FROM users u, items i
JOIN user_groups on user_groups.userId = u.id
AND user_groups.groupId = '3'
WHERE i.userId = u.id
AND i.datePosted BETWEEN CURDATE() AND NOW()
AND numPosted < 5

You can also use a > CURDATE() which is the exact same thing in a normal case, because if today(curdate) is 2012-09-16, every entry next to it will be today (but this is safer to use the previous query because if you use external services to recover / insert data, it may be something in the future...)

您也可以使用> CURDATE（），这在正常情况下完全相同，因为如果今天（curdate）是2012-09-16，那么它旁边的每个条目都将是今天（但使用前一个更安全查询，因为如果您使用外部服务来恢复/插入数据，它可能是未来...）

SELECT u.id, COUNT(i.id) as numPosted
FROM users u, items i
JOIN user_groups on user_groups.userId = u.id
AND user_groups.groupId = '3'
WHERE i.userId = u.id
AND i.datePosted > CURDATE()
AND numPosted < 5

#1