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 (...)
,字段不一致导致出错。
解决方案: 针对不同的原因进行修改
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)