Operand should contain 1 column(s)
翻译过来就是：操作数应包含一列；

错误原因:

往往是我们多出一列操作数，或者给的参数格式不正确；

解决方法：

Demo1:

WHERE msg_type IN (SELECT * FROM ies_message where msgsendtype=2)

注意：IN操作符后面的子查询是: SELECT *
IN操作符对应的只有一列操作数，而SELECT *回传的参数不只一列，导致该错误的产生；

Demo2:

GROUP BY(t.t1, t.t2)

改为

GROUP BY t.t1, t.t2

 

例二：

UPDATE ies_message set msgsendtype=3 where msgsendtype IN (SELECT * FROM ies_message where msgsendtype=2)
提示："#1241 - Operand should contain 1 column(s)"错误

子查询有问题，改为：
UPDATE ies_message set msgsendtype=3 where msgsendtype IN (SELECT
msgsendtype FROM ies_message where msgsendtype=2) 

 

例三:

Attempting to insert some data with MySQL using a subquery like so:

INSERT
INTO dw_plynjasa.jumlah_fact
    SELECT null, jasa_sk, waktu_sk, sum(hrg_pes), count(d.no_pes) 
    FROM   dw_plynjasa.jasa_dim      a,
           dw_plynjasa.waktu_dim     b,
           oltp_plynjasa.detil_pesan c,
           oltp_plynjasa.pesan       d,
           oltp_plynjasa.jasa        e
    WHERE  b.tgl = d.tgl_pes
      AND  a.kd_jasa = e.kd_jasa
      AND  a.nm_jasa = e.nm_jasa
      AND  a.satuan = e.satuan
      AND  a.hrg_satuan = e.hrg_satuan 
    GROUP  BY (jasa_sk, waktu_sk);

but I get only error 1241, operand should contain 1 column(s)

解答:

group by should be on individual columns and comma separated.
They should not be a grouped as a set.

Remove parenthesis ( and ) in group by clause.

Example:

mysql> select * from so.employee where 1=2 group by empno, deptno;
Empty set (0.00 sec)

mysql> select * from so.employee where 1=2 group by (empno, deptno);
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql>

 

例四：

select 
 pit_key
,employee_code
,department_id
,value_date
from pit_employee_department ped
where ped.employee_code = 'GSCQ3349'
and ped.value_date < date_format(date_sub(curdate(), interval day(curdate()) - 1 day),'%Y%m%d')
and ped.pit_key not in
(	select 
	pit_key
	,value_date
	from pit_employee_department ped_1
	inner join 
	(
		select 
		max(value_date) as max_date
		from pit_employee_department ped

		where ped.value_date <= date_format(date_sub( date_sub(curdate(), interval day(curdate()) - 1 day),interval 1 month),'%Y%m%d')
		and employee_code = 'GSSH0039'
	)ped_2
	on ped_1.value_date < ped_2.max_date
	and ped_1.employee_code = 'GSSH0039'

 解答:

执行上面的SQL 报这个错的原因是：在子查询中使用了pit_key 和 value_date两个字段，而比较的选项是pit_key not in (...) ，字段不一致导致出错。

解决方案:       针对不同的原因进行修改