MySQL - 操作数应包含 1 列
在我创建的系统上工作时，我尝试在我的项目中使用以下查询:
While working on a system I'm creating, I attempted to use the following query in my project:
SELECT
topics.id,
topics.name,
topics.post_count,
topics.view_count,
COUNT( posts.solved_post ) AS solved_post,
(SELECT users.username AS posted_by,
users.id AS posted_by_id
FROM users
WHERE users.id = posts.posted_by)
FROM topics
LEFT OUTER JOIN posts ON posts.topic_id = topics.id
WHERE topics.cat_id = :cat
GROUP BY topics.id
":cat" 在我使用 PDO 时受我的 PHP 代码约束.2 是 ":cat" 的有效值.
":cat" is bound by my PHP code as I'm using PDO. 2 is a valid value for ":cat".
该查询虽然给了我一个错误:#1241 - 操作数应该包含 1 列"
That query though gives me an error: "#1241 - Operand should contain 1 column(s)"
让我难受的是，我认为这个查询没有问题.选择列，然后从另一个表中再选择两个，然后从那里继续.我就是想不通是什么问题.
What stumps me is that I would think that this query would work no problem. Selecting columns, then selecting two more from another table, and continuing on from there. I just can't figure out what the problem is.
是否有一个简单的解决方法，或者其他编写查询的方法?
Is there a simple fix to this, or another way to write my query?
推荐答案
您的子查询正在选择两列，而您正在使用它来投影一列(作为外部 SELECT 子句的一部分).在此上下文中，您只能从此类查询中选择一列.
Your subquery is selecting two columns, while you are using it to project one column (as part of the outer SELECT clause). You can only select one column from such a query in this context.
考虑加入users 表；在从 users 中选择您想要的列时，这将为您提供更大的灵活性.
Consider joining to the users table instead; this will give you more flexibility when selecting what columns you want from users.
SELECT
topics.id,
topics.name,
topics.post_count,
topics.view_count,
COUNT( posts.solved_post ) AS solved_post,
users.username AS posted_by,
users.id AS posted_by_id
FROM topics
LEFT OUTER JOIN posts ON posts.topic_id = topics.id
LEFT OUTER JOIN users ON users.id = posts.posted_by
WHERE topics.cat_id = :cat
GROUP BY topics.id
相关文章