导致错误的原因(操作数应包含 1 列)

Question

我正在尝试设置 myItemId，以便我可以在 concat 查询中使用它。一切正常，直到我添加此行

  SET myItemID = (SELECT * FROM items i  WHERE i.name LIKE '%KW PRE FREE COOLING%');

然后它给了我一个错误操作数应包含 1 列

这是我正在使用的查询

CREATE PROCEDURE reportFreeCoolingTempTable (
  IN fromDate VARCHAR (50),
  IN toDate   VARCHAR (50),
  IN timeZone VARCHAR (50)
)
BEGIN
  DECLARE startDate VARCHAR (50);
  DECLARE endDate   VARCHAR (50);
  DECLARE mylogID   INT;
  DECLARE myItemID  int;
  SET startDate = FROM_UNIXTIME(fromDate/1000);
  SET endDate   = FROM_UNIXTIME(toDate/1000);
  SET mylogID   = (SELECT logID FROM logs WHERE details LIKE 'FCT%' LIMIT 1);
  SET myItemID = (SELECT * FROM items i  WHERE i.name LIKE '%KW PRE FREE COOLING%');
  SET @sql = NULL;
  SET @sql = NULL;
  SET @sql = CONCAT(
  'SELECT @row:=@row+1 as rownum,
       a.logid ,   
       L1.recordId,
       L2.recordId as next_recordId,
       L1.completed,
       L2.completed as next_completed,
       L1.activityId,
       L2.activityId as next_activityId,
       IF(L1.activityId = L2.activityId,1,NULL) as isError,                           
       TIME_TO_SEC(TIMEDIFF(L2.completed, L1.completed)) / 3600 AS coolingHours,
       ((L1.item31985 - L1.item31987) * (time_to_sec(timediff(L2.completed, L1.completed)))) / 3600  AS kwDifference,
     ((L1.item31985 - L1.item31987) * (substr(l.details, instr(l.details , '':'' ) +1))) AS cost,
    ( (((L1.item31985 - L1.item31987) * (substr(l.details, instr(l.details , '':'' ) +1)))
    *(time_to_sec(timediff(L2.completed, L1.completed)) / 3600))) AS costT,
     time_to_sec(timediff(''', endDate, ''', ''', startDate, ''')) / 3600 AS totalTimeRange,
    CONVERT_TZ(''', startDate, ''', ''UTC'', ''', timeZone, ''') AS startingDate, 
    CONVERT_TZ(''', endDate, ''', ''UTC'', ''', timeZone, ''') AS endingDate,
    DATABASE() AS databaseName
FROM
    (SELECT @row:=0)R,
    (SELECT T1.completed,
       (SELECT MIN(completed)
         FROM log1644
         WHERE completed > T1.completed) AS next_completed
      FROM log',mylogID, ' T1
      ORDER BY T1.completed
     )TimeOrder
        LEFT JOIN log', mylogID, ' L1 ON (L1.completed = TimeOrder.completed)
        LEFT JOIN log', mylogID, ' L2 ON (L2.completed = TimeOrder.next_completed)
        LEFT JOIN activities a ON L1.activityId = a.activityId
        LEFT JOIN logs l ON a.logId = l.logId
        Left Join items i ON l.logId = i.logId AND i.name LIKE ''%KW%''
    WHERE i.itemID = 31985  
        AND L1.completed BETWEEN ''', startDate, ''' AND ''', endDate, '''
ORDER BY L1.completed');

 PREPARE stmt FROM @sql;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

Answer 1

错误本身解释了(operands should contain 1 column)您需要从查询中选择单个列才能设置myItemID，您正在从items中选择所有列 试试这个

SET myItemID = (SELECT id FROM items i  WHERE i.name LIKE '%KW PRE FREE COOLING%' LIMIT 1 );

我假设您需要将条件匹配的项目的 id 列myItemID设置，我还添加了LIMIT 1以避免子查询应返回一个结果的错误

Answer 2

导致此错误的原因是 SET 语句期望从子查询返回单个值。 它不仅可以返回多个值 （SELECT *），还可以返回多行。 更改查询以仅指定子查询中要分配给 myItemId 的单个列，并确保它只能返回 1 行 - 如下所示：

SET myItemID = (SELECT TOP 1 [itemIdColumnName] FROM items i  WHERE i.name LIKE '%KW PRE FREE COOLING%');

Answer 3

您案例中的"操作数"是"myItemID"。 它只能保存一个值。 SELECT 语句返回表中的所有行（多列）。 您只需选择代表您尝试获取的 ID 的 1 列。