将MySQL查询时间间隔从23小时改为24小时触发ERROR 1242错误的排查与解决请求

错误根源分析 #

你遇到的这个错误和跨天其实没什么直接关系，核心问题是：当你把时间间隔从23小时改成24小时后，你的子查询返回了多条记录。SQL里像>= (子查询)这类比较操作，要求子查询必须返回恰好1行数据，否则就会触发1242错误。

之前用23小时的时候，DWNTIM表中符合start_date BETWEEN ...条件的记录刚好只有1条，所以子查询返回单行，和>=/<=的比较完全没问题；但改成24小时后，时间范围扩大，匹配到了多条shift记录，子查询返回多行，自然就触发了错误。

先验证问题 #

你可以单独执行其中一个出问题的子查询，确认返回行数：

SELECT cast(concat(start_date, ' ', (SEC_TO_TIME(start_time*60))) as datetime) 
FROM `austcor_map`.`DWNTIM` 
WHERE shift_id_number = 2 
  AND machine_number = 35 
  AND start_date BETWEEN (CURDATE()-INTERVAL 1 DAY) AND DATE_ADD((CURDATE()-INTERVAL 1 DAY), INTERVAL 24 HOUR)

如果返回行数大于1，就完全证实了这个问题。

具体解决方案 #

根据你的业务需求，有几种常见的修复方式：

1. 用聚合函数确保子查询返回单行

如果你需要取这个时间范围内最早的shift开始时间或者最晚的shift结束时间，可以用MIN()/MAX()聚合函数包裹子查询，强制返回单行值：

修改后的查询示例：

SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY) AS SHIFTDATE, t1.start_date_time, t1.end_date_time, 
CASE WHEN t1.start_date_time >= (
  SELECT MIN(cast(concat(start_date, ' ', (SEC_TO_TIME(start_time*60))) as datetime)) 
  FROM `austcor_map`.`DWNTIM` 
  WHERE shift_id_number = 2 AND machine_number = 35 
    AND start_date BETWEEN (CURDATE()-INTERVAL 1 DAY) AND DATE_ADD((CURDATE()-INTERVAL 1 DAY), INTERVAL 24 HOUR)
) 
AND t1.end_date_time <= (
  SELECT MAX(cast(concat(finish_date, ' ', (SEC_TO_TIME(finish_time*60))) as datetime)) 
  FROM `austcor_map`.`DWNTIM` 
  WHERE shift_id_number = 2 AND machine_number = 35 
    AND finish_date BETWEEN (CURDATE()-INTERVAL 1 DAY) AND DATE_ADD((CURDATE()-INTERVAL 1 DAY), INTERVAL 24 HOUR)
) 
THEN 'DAY' END AS Shift 
FROM `austcor_map`.`PDETLH` t1 
WHERE t1.start_date_time >= (
  SELECT MIN(cast(concat(start_date, ' ', (SEC_TO_TIME(start_time*60))) as datetime)) 
  FROM `austcor_map`.`DWNTIM` 
  WHERE shift_id_number = 2 AND machine_number = 35 
    AND start_date BETWEEN (CURDATE()-INTERVAL 1 DAY) AND DATE_ADD((CURDATE()-INTERVAL 1 DAY), INTERVAL 24 HOUR)
) 
AND t1.end_date_time <= (
  SELECT MAX(cast(concat(finish_date, ' ', (SEC_TO_TIME(finish_time*60))) as datetime)) 
  FROM `austcor_map`.`DWNTIM` 
  WHERE shift_id_number IN (2) AND machine_number = 35 
    AND finish_date BETWEEN (CURDATE()-INTERVAL 1 DAY) AND DATE_ADD((CURDATE()-INTERVAL 1 DAY), INTERVAL 24 HOUR)
) 
AND t1.corrugator_number = 1 
GROUP BY PROGRAM WITH ROLLUP

2. 用JOIN重构查询（更高效清晰）

你的查询里多次重复了相同的子查询，不仅效率低，也容易出错。可以用CTE（公共表表达式）先获取需要的shift时间范围，再通过JOIN关联查询：

WITH ShiftTimeRange AS (
  SELECT 
    MIN(cast(concat(start_date, ' ', (SEC_TO_TIME(start_time*60))) as datetime)) AS shift_start,
    MAX(cast(concat(finish_date, ' ', (SEC_TO_TIME(finish_time*60))) as datetime)) AS shift_end
  FROM `austcor_map`.`DWNTIM` 
  WHERE shift_id_number = 2 
    AND machine_number = 35 
    AND start_date BETWEEN (CURDATE()-INTERVAL 1 DAY) AND DATE_ADD((CURDATE()-INTERVAL 1 DAY), INTERVAL 24 HOUR)
)
SELECT 
  DATE_SUB(CURDATE(),INTERVAL 1 DAY) AS SHIFTDATE, 
  t1.start_date_time, 
  t1.end_date_time, 
  CASE WHEN t1.start_date_time >= str.shift_start AND t1.end_date_time <= str.shift_end THEN 'DAY' END AS Shift 
FROM `austcor_map`.`PDETLH` t1 
CROSS JOIN ShiftTimeRange str
WHERE t1.start_date_time >= str.shift_start 
  AND t1.end_date_time <= str.shift_end 
  AND t1.corrugator_number = 1 
GROUP BY PROGRAM WITH ROLLUP

3. 调整筛选条件（如果业务需要特定shift）

如果你的业务逻辑是只需要匹配某一个特定的shift，而不是时间范围内的所有shift，可以在DWNTIM的WHERE条件里添加更精确的筛选（比如特定的shift ID），确保子查询只返回单行数据。

内容的提问来源于stack exchange，提问作者Marc