MySQL-操作数应包含1列 [英] MySQL - Operand should contain 1 column(s)

问题描述

在我正在创建的系统上工作时，我尝试在项目中使用以下查询:

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

这篇关于MySQL-操作数应包含1列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！