postgre组合两个JPA Spring查询SQL查询

Question

我创建了两个自定义SQL查询来从PostgreSQL数据库中获取数据。我对一个小改动提出了两个问题。用户ID部分。使用wd.id，它会返回所有工人报告，但有时我需要获得一个特定的工人报告。有没有办法将这两个查询结合起来？

@Query(value = "SELECTn" +
"twd.id as workDetailId,n" +
"    max(ua.name) as name,n" +
"    count(sw.id) as shiftCount,n" +
"    sum(sw.actual_work_duration) as workDuration,n" +
"    sum(sw.actual_shift_duration) as shiftDuration,n" +
"twd.salary as salary,n" +
"tmax(mi.name) as jobRelationn" +
"FROM shift_worker swn" +
"JOIN user_acc ua ON ua.work_detail_id = sw.work_detail_idn" +
"JOIN work_detail wd ON sw.work_detail_id = wd.idn" +
"JOIN maintenance_item mi ON wd.job_relation_id = mi.idn" +
"WHERE sw.work_detail_id = wd.id ANDn" +
"    sw.actual_work_start_time > ?1 ANDn" +
"    sw.actual_work_start_time < ?2 AND sw.actual_work_end_time IS NOT NULLn" +
"GROUP BY wd.id", nativeQuery = true)
Page<UserShiftReport> findUserReports(LocalDateTime startDateTime, LocalDateTime endDateTime, Pageable pageable);
@Query(value = "SELECTn" +
"twd.id as workDetailId,n" +
"    max(ua.name) as name,n" +
"    count(sw.id) as shiftCount,n" +
"    sum(sw.actual_work_duration) as workDuration,n" +
"    sum(sw.actual_shift_duration) as shiftDuration,n" +
"twd.salary as salary,n" +
"tmax(mi.name) as jobRelationn" +
"FROM shift_worker swn" +
"JOIN user_acc ua ON ua.work_detail_id = sw.work_detail_idn" +
"JOIN work_detail wd ON sw.work_detail_id = wd.idn" +
"JOIN maintenance_item mi ON wd.job_relation_id = mi.idn" +
"WHERE sw.work_detail_id = ?1 ANDn" +
"    sw.actual_work_start_time > ?2 ANDn" +
"    sw.actual_work_start_time < ?3 AND sw.actual_work_end_time IS NOT NULLn" +
"GROUP BY wd.id", nativeQuery = true)
Page<UserShiftReport> findUserReportByUserId(Long userId, LocalDateTime startDateTime, LocalDateTime endDateTime, Pageable pageable);

Answer 1

如果可以合并保留userId参数的两个方法，在需要通用结果的地方传递一个0 userId，并假设没有userId等于0的数据，则可以将查询组合为-

Where sw.work_detail_id = case when ?1 = 0 then wd.id else ?1 end

本质上是查询中where子句中不同列的"case-when"语法用法。