猿问
下载APP

操作数应包含1列 - MySQL不在

操作数应包含1列 - MySQL不在

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 < d.frequency)

我得到错误“操作数应该包含1列” - 但我需要COUNT(e.id)


料青山看我应如是
浏览 52回答 3
3回答

白衣染霜花

这总是这样的:select&nbsp;*from&nbsp;campaigns&nbsp; where&nbsp;id&nbsp;not&nbsp;in&nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;id_campaign&nbsp;from&nbsp;( &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;select&nbsp;e.id_campaign&nbsp;as&nbsp;id_campaign,&nbsp;d.frequency,&nbsp;e.id&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;from&nbsp;campaigns&nbsp;d&nbsp;left&nbsp;join&nbsp;served&nbsp;e&nbsp;on&nbsp;d.id&nbsp;=&nbsp;e.id_campaign&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;where&nbsp;d.status&nbsp;=&nbsp;'Active' &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;group&nbsp;by&nbsp;e.id_campaign&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;having&nbsp;count(e.id)&nbsp;<&nbsp;d.frequency&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;))

侃侃无极

'not in'子句需要一个值列表。因此,以下查询将起作用:SELECT&nbsp;*&nbsp;from&nbsp;campaigns&nbsp;WHERE&nbsp;id&nbsp;not&nbsp;in(SELECT&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;e.id_campaign&nbsp;&nbsp;&nbsp;&nbsp;FROM&nbsp;campaigns&nbsp;d&nbsp;&nbsp;&nbsp;&nbsp;LEFT&nbsp;JOIN&nbsp;served&nbsp;e&nbsp;&nbsp;&nbsp;&nbsp;ON&nbsp;d.id&nbsp;=&nbsp;e.id_campaign&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;WHERE&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;d.status&nbsp;=&nbsp;'Active' &nbsp;&nbsp;&nbsp;&nbsp;GROUP&nbsp;BY&nbsp;e.id_campaign&nbsp;&nbsp;&nbsp;&nbsp;HAVING &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;COUNT(e.id)&nbsp;<&nbsp;d.frequency)如果您可以告诉我们表的结构以及您想要选择的内容,我们将能够为您找出正确的查询。

POPMUISE

这取决于数据库。您通常可以使用LEFT JOIN而不是其他子查询,但HAVING要求会使事情复杂化。我通常只是尝试清楚地表达逻辑并让数据库弄清楚,如果它太慢,那么你看一下数据,索引和查询计划并开始调整。
打开App,查看更多内容
随时随地看视频慕课网APP
我要回答