如果没有GROUP BY子句，则将GROUP列(MIN()，MAX()，COUNT()，...)与GROUP列混合使用是非法的 [英] Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

问题描述

我使用的是旧的php脚本，此查询有错误.由于我不熟悉mysql，因此无法修复.

I'm using an old php script and have an error with this query. Since I'm not experienced with mysql, I couldn't fix it.

"SELECT COUNT(p.postid) AS pid, p.*, t.* FROM ".TABLE_PREFIX."post AS p 
INNER JOIN ".TABLE_PREFIX."thread AS t ON (p.threadid = t.threadid) WHERE
p.username='".$uname."'" 

错误是

如果没有GROUP BY子句，则不具有GROUP列的GROUP列(MIN()，MAX()，COUNT()等)的混合是非法的

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

我希望有人能帮助我

推荐答案

就像错误说的那样，如果不对行进行显式(使用GROUP BY)或隐式(仅通过分组)分组，就无法选择诸如COUNT之类的聚合函数选择汇总).用较少的技术术语来说-您要告诉数据库使用此用户名查找所有帖子，并查看它们所属的线程以及帖子的数量"，而数据库正在回答您，帖子的数量在什么地方?".

Like the error says, you can't SELECT an aggregate function, such as COUNT, without grouping rows either explicitly (using GROUP BY) or implicitly (by just selecting the aggregate). To put it in less technical terms - you're telling the database, "Look up all posts by this username, and the threads they belong to, and the number of posts", and the database is answering you, "the number of posts in what?".

因此，您需要更加具体.如果您真正想要的是:

So you'll need to be more specific. If what you actually want is:

• 如果您实际上并不关心所有单个帖子，而只希望该线程和该用户每个线程的帖子数量，请从SELECT中删除p.*，并在最后添加GROUP BY t.threadid查询.
• 如果您想要发布的总数以及所有发布和主题，只需计算结果行即可.
• 如果您要选择的线程中所有用户的帖子总数，这会使查询更加复杂，因为您需要再次加入post才能获得帖子总数.
• 如果您实际上根本不需要计数，请从查询中将其删除. :)

• If you don't actually care about all the individual posts, and you just want the threads and the number of posts by this user per thread, remove the p.* from the SELECT, and add GROUP BY t.threadid to the end of the query.
• If you want the total number of posts as well as all the posts and threads, just count the result rows.
• If you want the total number of posts by all users in the threads you're selecting, that'll make the query much more complex, because you'll need to join on post again to get the total post count.
• If you don't actually want the count at at all, remove it from the query. :)

这篇关于如果没有GROUP BY子句，则将GROUP列(MIN()，MAX()，COUNT()，...)与GROUP列混合使用是非法的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！