java - MySQL插入与选择不同

我在mysql上使用java，我在mysql中有此代码

SELECT DISTINCT date(datecreat) as datetemp, (SELECT max(sensortemp) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as maxt, (SELECT min(sensortemp) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as mint, (SELECT avg(sensortemp) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as avgt, (SELECT max(sensorhum) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as maxh, (SELECT min(sensorhum) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as minh, (SELECT avg(sensorhum) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as avgh, (SELECT max(sensorpres) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as maxp, (SELECT min(sensorpres) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as minp, (SELECT avg(sensorpres) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) as avgp FROM akdb.iotdatas where (serialnum ='0000000000000');

结果是这样的：


但是我想使用此代码将此数据插入其他表

insert into akdb.climdatas(hightemp,lowtemp,avgtemp,highhum,lowhum,avghum,highpress,lowpress,avgpress,dateinsert,zonedatas)values(maxt,mint,avgt,maxh,minh,avgh,maxp,minp,avgp,datetemp,'zone h');

这里的问题是“ maxt mint ...”是未知的，但是如果我使用select into insert

insert into akdb.climdatas(SELECT DISTINCT date(datecreat) as datetemp ,(SELECT max(sensortemp) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT min(sensortemp) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT avg(sensortemp) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT max(sensorhum) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT min(sensorhum) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT avg(sensorhum) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT max(sensorpres) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT min(sensorpres) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ) , (SELECT avg(sensorpres) FROM akdb.iotdatas WHERE date(datecreat)=datetemp ),' zone h'FROM akdb.iotdatas where (serialnum ='0000000000000000'))

错误在列“ mysql列计数与第1行的值计数不匹配”中

最佳答案

似乎您在climdatas表中有多于或少于11列。在您的插入语句中，specify list of columns separated by comma。

insert into akdb.climdatas (dateinsert,hightemp,lowtemp,avgtemp,highhum,lowhum,avghum,highpress,lowpress,avgpress,zonedatas)
(your select query goes here.)

关于java - MySQL插入与选择不同，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44690759/