Mengapa Pertanyaan MySQL `NOT IN` dengan `COUNT()` Menghasilkan Ralat 'Operand Should Contain 1 Column'?

MySQL "Operand Should Contain 1 Column" Ralat dalam NOT IN Subqueries dengan Fungsi Agregat

Menggunakan NOT IN subqueries dalam MySQL memerlukan perhatian yang teliti terhadap kiraan lajur. Ralat biasa, "Operand harus mengandungi 1 lajur," timbul apabila subkueri menggunakan fungsi agregat seperti COUNT(), mengembalikan berbilang lajur. Pengendali NOT IN MySQL menjangkakan perbandingan satu lajur.

Punca Punca:

Ralat berpunca daripada ketidakpadanan dalam bilangan lajur yang dikembalikan oleh pertanyaan utama dan subkueri. Subkueri NOT IN, yang mengandungi fungsi agregat, menghasilkan set hasil dengan lebih daripada satu lajur, bercanggah dengan lajur tunggal id dalam klausa WHERE pertanyaan utama.

Contoh Ilustrasi:

Pertimbangkan pertanyaan bermasalah ini:

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="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="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`.

Atas ialah kandungan terperinci Mengapa Pertanyaan MySQL `NOT IN` dengan `COUNT()` Menghasilkan Ralat 'Operand Should Contain 1 Column'?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!