MySQL获取两表最新日期报错及Spring JPA集成方案问询

一、语法错误原因及修复 #

1. 核心原因：MySQL版本不支持CTE #

你的SQL报错是因为MySQL 5.7及以下版本不支持WITH子句（CTE），仅MySQL 8.0及以上版本支持该语法。若数据库版本低于8.0，就会触发此语法错误。

2. 兼容全版本的替代写法 #

用子查询替代CTE，同时修正你原SQL中无效的JOIN条件（r.warehouse_id = r.warehouse_id改为r.warehouse_id = w.id）：

SELECT MAX(date_to) AS dateTo
FROM (
    SELECT a.date_to
    FROM aaa a
    JOIN ramp r ON a.ramp_id = r.id
    JOIN warehouse w ON r.warehouse_id = w.id
    WHERE w.id = 222
      AND a.date_from >= '2022-08-20T00:00'
    
    UNION ALL  -- 无需去重时用UNION ALL，性能更优；需去重则保留UNION
    SELECT b.valid_to AS date_to
    FROM bbb b
    WHERE b.warehouse_id = 222
      AND b.status = 'ACTIVE'
) AS allDates

3. MySQL 8.0+版本的CTE修正写法 #

若确认数据库为MySQL 8.0及以上，修正后的CTE写法如下：

WITH allDates AS (
    SELECT a.date_to
    FROM aaa a
    JOIN ramp r ON a.ramp_id = r.id
    JOIN warehouse w ON r.warehouse_id = w.id
    WHERE w.id = 222
      AND a.date_from >= '2022-08-20T00:00'
    
    UNION ALL
    SELECT b.valid_to AS date_to
    FROM bbb b
    WHERE b.warehouse_id = 222
      AND b.status = 'ACTIVE'
)
SELECT MAX(date_to) AS dateTo
FROM allDates

二、Spring JPA Repository原生查询实现 #

1. 定义Repository接口 #

创建自定义Repository，通过@Query标记原生查询：

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import java.util.Optional;

public interface DateRepository extends JpaRepository<YourEntity, Long> {

    @Query(value = """
        SELECT MAX(date_to) AS dateTo
        FROM (
            SELECT a.date_to
            FROM aaa a
            JOIN ramp r ON a.ramp_id = r.id
            JOIN warehouse w ON r.warehouse_id = w.id
            WHERE w.id = :warehouseId
              AND a.date_from >= :startDate
            
            UNION ALL
            SELECT b.valid_to AS date_to
            FROM bbb b
            WHERE b.warehouse_id = :warehouseId
              AND b.status = 'ACTIVE'
        ) AS allDates
        """, nativeQuery = true)
    Optional<String> getLatestDate(@Param("warehouseId") Integer warehouseId, 
                                  @Param("startDate") String startDate);
}

2. 关键说明 #

• 空值处理：用Optional作为返回值，优雅处理两张表均无数据时返回NULL的场景，避免空指针。
• 参数传递：使用命名参数（@Param）增强代码可读性，也可使用位置占位符（?1、?2）。
• 性能优化：优先用UNION ALL替代UNION，减少去重带来的性能开销（若业务无需去重）。

3. 业务层调用示例 #

@Autowired
private DateRepository dateRepository;

public String getLatestWarehouseDate() {
    Optional<String> latestDate = dateRepository.getLatestDate(222, "2022-08-20T00:00");
    return latestDate.orElse(null); // 或返回自定义默认值
}

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