时间戳

首页 > 解决方案 > 结合两个 JPA Spring 查询 SQL 查询

sql - 结合两个 JPA Spring 查询 SQL 查询

问题描述

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

@Query(value = "SELECT\n" +
            "\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 jobRelation\n" +
            "FROM shift_worker sw\n" +
            "JOIN user_acc ua ON ua.work_detail_id = sw.work_detail_id\n" +
            "JOIN work_detail wd ON sw.work_detail_id = wd.id\n" +
            "JOIN maintenance_item mi ON wd.job_relation_id = mi.id\n" +
            "WHERE sw.work_detail_id = wd.id AND\n" +
            "    sw.actual_work_start_time > ?1 AND\n" +
            "    sw.actual_work_start_time < ?2 AND sw.actual_work_end_time IS NOT NULL\n" +
            "GROUP BY wd.id", nativeQuery = true)
Page<UserShiftReport> findUserReports(LocalDateTime startDateTime, LocalDateTime endDateTime, Pageable pageable);

@Query(value = "SELECT\n" +
            "\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 jobRelation\n" +
            "FROM shift_worker sw\n" +
            "JOIN user_acc ua ON ua.work_detail_id = sw.work_detail_id\n" +
            "JOIN work_detail wd ON sw.work_detail_id = wd.id\n" +
            "JOIN maintenance_item mi ON wd.job_relation_id = mi.id\n" +
            "WHERE sw.work_detail_id = ?1 AND\n" +
            "    sw.actual_work_start_time > ?2 AND\n" +
            "    sw.actual_work_start_time < ?3 AND sw.actual_work_end_time IS NOT NULL\n" +
            "GROUP BY wd.id", nativeQuery = true)
Page<UserShiftReport> findUserReportByUserId(Long userId, LocalDateTime startDateTime, LocalDateTime endDateTime, Pageable pageable);

标签： sqlpostgresql

解决方案

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

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

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

推荐阅读