NOT IN 具有聚合函数的子查询中的 MySQL“操作数应包含 1 列”错误
在 MySQL 中使用 NOT IN 子查询需要仔细注意列计数。 当子查询使用 COUNT() 等聚合函数返回多列时，会出现一个常见错误“操作数应包含 1 列”。 MySQL 的 NOT IN 运算符需要单列比较。
根本原因：
该错误源于主查询和子查询返回的列数不匹配。 包含聚合函数的 NOT IN 子查询会生成包含多个列的结果集，与主查询的 id 子句中的单列 WHERE 冲突。
示例：
考虑这个有问题的查询：
<code class="language-sql">
SELECT *
FROM campaigns
WHERE id NOT IN
(
SELECT
e.id_campaign,
d.name,
d.frequency,
d.country,
d.referral,
d.bid,
d.status,
COUNT(e.id) AS countcap
FROM campaigns d
LEFT JOIN served e
ON d.id = e.id_campaign
WHERE
d.status = 'Active'
GROUP BY
e.id_campaign
HAVING
countcap < p>The intention is to select campaigns *not* included in the subquery's results. The subquery, however, returns eight columns, causing the "Operand should contain 1 column" error because `NOT IN` expects a single-column comparison against the `id` column in the `campaigns` table.</p><p>**Resolution:**</p><p>The solution involves restructuring the subquery to return only the `id_campaign` column:</p><pre class="brush:php;toolbar:false"><code class="language-sql">SELECT *
FROM campaigns
WHERE id NOT IN
(
SELECT e.id_campaign
FROM campaigns d
LEFT JOIN served e ON d.id = e.id_campaign
WHERE d.status = 'Active'
GROUP BY e.id_campaign
HAVING COUNT(e.id) <
</code>Alternatively, for situations requiring multiple columns, use `EXISTS` or `NOT EXISTS` for a more efficient and accurate solution:
<code class="language-sql">SELECT *
FROM campaigns c
WHERE NOT EXISTS
(
SELECT 1
FROM campaigns d
INNER JOIN served e ON d.id = e.id_campaign
WHERE d.id = c.id
AND d.status = 'Active'
AND COUNT(e.id) <
</code>This revised approach avoids the column count mismatch and provides a cleaner solution for scenarios involving aggregate functions within subqueries used with `NOT IN`.
以上是为什么我的 MySQL `NOT IN` 查询使用 `COUNT()` 会导致'操作数应包含 1 列”错误？的详细内容。更多信息请关注PHP中文网其他相关文章！
