MySQL 错误：操作数应包含 1 列

MySQL error: Operand should contain 1 column(s)

提问by Lucy Amalia Lusiana

I have this MySQL Query (working)

我有这个 MySQL 查询（工作）

First query:

第一个查询：

SELECT id
FROM users
WHERE publisher_set = '1'
AND publisher_status = '1'
AND publisher_content != ''
AND publisher_amount != '0'
AND publisher_now < publisher_max
AND EXISTS (

SELECT *
FROM user_counter
WHERE users.id = user_counter.publisher_id
)

The MySQL query above is to find the user id from two table

上面的 MySQL 查询是从两个表中查找用户 id

Now I want to compared again using this second MySQL query (working)

现在我想使用第二个 MySQL 查询（工作）再次进行比较

Second query:

第二个查询：

SELECT users.id, publisher_amount, publisher_now, publisher_max, counter
FROM users
INNER JOIN user_counter ON users.id = user_counter.publisher_id
WHERE no = 08123456789
AND counter < publisher_amount

But when I join all the query like this:

但是当我像这样加入所有查询时：

SELECT id
FROM users
WHERE publisher_set = '1'
AND publisher_status = '1'
AND publisher_content != ''
AND publisher_amount != '0'
AND publisher_now < publisher_max
AND EXISTS (

SELECT *
FROM user_counter
WHERE users.id = user_counter.publisher_id
)

AND (
SELECT users.id, publisher_amount, publisher_now, publisher_max, counter
FROM users
INNER JOIN user_counter ON users.id = user_counter.publisher_id
WHERE no =08123456789
AND counter < publisher_amount
)

I get this error:

我收到此错误：

Operand should contain 1 column(s)

操作数应包含 1 列

Then, I try using 1 column, but the result is not what I wanted.

然后，我尝试使用 1 列，但结果不是我想要的。

My question is How to join first and second query ? and produce no error.

我的问题是如何加入第一个和第二个查询？并且不会产生错误。

I have tried google it and after many "try-and-error" this is the far I can get to make the query work.

我试过谷歌它，经过多次“尝试和错误”之后，这是我可以使查询工作的距离。

回答by fthiella

I think you just miss an EXISTS on your second subquery. Anyway, if I understand your query correctly, I think you could write your query as this:

我认为您只是错过了第二个子查询中的 EXISTS。无论如何，如果我正确理解您的查询，我认为您可以将查询写为：

SELECT
  u.id
FROM
  users u inner join user_counter uc
  on u.id=uc.publisher_id
     and no=08123456789
     and counter < publisher_amount
WHERE
  u.publisher_set = '1'
  AND u.publisher_status = '1'
  AND u.publisher_content != ''
  AND u.publisher_amount != '0'
  AND u.publisher_now < publisher_max

回答by GabCas

You could change the first EXISTS clause to:

您可以将第一个 EXISTS 子句更改为：

and users.id in (select user_counter.publisher_id from user_counter) 

and use EXISTS on the final query.

并在最终查询中使用 EXISTS。

回答by bonCodigo

You could also do this:

你也可以这样做：

AND EXISTS (

SELECT user_counter.publisher_id
FROM user_counter
WHERE users.id = user_counter.publisher_id

---

---

PS: SQLFIDDLE doesn't work in my end for some reason. Else would have been happy to give you a demonstration ;)

PS：由于某种原因，SQLFIDDLE 最终不起作用。否则很乐意为您演示;)

回答by TJChambers

SELECT id
FROM users
WHERE publisher_set = '1'
AND publisher_status = '1' 
AND publisher_content != ''
AND publisher_amount != '0'
AND publisher_now < publisher_max
AND EXISTS (
select 1 from  user_counter where  users.id = user_counter.publisher_id
    and  no =08123456789
AND counter < publisher_amount
)

Assuming noand counterare on table user_counter. A bit hard to tell without a schema.

假设no和counter都在桌子上user_counter。没有架构就很难说。