在hibernate（springboot）中编写带有左联接和分页的sql本机查询[duplicate]

Question

我正在使用spring data JPA，我想在我的存储库中编写一个SQL查询。

我有一个下面的SQL查询（注意左边的连接）：

SELECT * FROM institution LEFT JOIN 
(select * from building_institutions where building_institutions.building_id = 1) as reserved_institutions
ON reserved_institutions.institutions_user_id = institution.user_id
WHERE reserved_institutions.institutions_user_id is null;

我想在我的机构存储库中执行它，如下所示：

@Repository
public interface InstitutionRepository extends JpaRepository<Institution, Long>, PagingAndSortingRepository<Institution,Long> {
    // doesn't work
    //@Query("SELECT b.institutions as bi FROM Building b left join Institution i WHERE bi.building_id not in :id")
    Page<Institution> findPotentialInstitutionsByBuildingId(@Param("id") Collection<Long> id, Pageable pageable);

    // doesn't work    
    @Query(
            value = "SELECT * FROM kits_nwt.institution LEFT JOIN\n" +
                    "(SELECT * FROM kits_nwt.building_institutions WHERE kits_nwt.building_institutions.building_id = ?1) AS reserved_institutions\n" +
                    "ON reserved_institutions.institutions_user_id = kits_nwt.institution.user_id\n" +
                    "WHERE reserved_institutions.institutions_user_id IS null ORDER BY ?#{#pageable}",
            nativeQuery = true)
    Page<Institution> findPotentialInstitutionsByBuildingId(Long userId, Pageable pageable);
}

我想要的内容：（在此查询中，id设置为1，用于表示目的）

我看过很多这样的问题和答案（比如这一个），但我还没能找到答案。

那么，我如何编写这个查询，以便得到我想要的东西呢？

@Query(
        value = "SELECT username, password, description, location, title, user_id FROM (institution INNER JOIN user ON institution.user_id = user.id) LEFT JOIN\n" +
                "(SELECT * FROM building_institutions WHERE building_institutions.building_id = 1) AS reserved_institutions\n" +
                "ON reserved_institutions.institutions_user_id = kits_nwt.institution.user_id\n" +
                "WHERE reserved_institutions.institutions_user_id IS null ORDER BY ?#{#pageable}",
        nativeQuery = true)
Page<Institution> findPotentialInstitutionsByBuildingId(Long userId, Pageable pageable);

我得到了一个例外：

mysqlsyntaxerrorexception：您的SQL语法中有错误；查看与您的MySQL server版本相对应的手册，了解第2行“WHERE)FROM building_institutionswhere building_institutions.building_id=1)a'附近使用的正确语法

@Stanislavl这里是：

问题是，您的查询给我的是ID为25的机构，因为它同时位于ID为1的大楼和ID为2的大楼中。当您执行连接时，在institution表和building_institutions表中都有2行InstitutionID为25的行。然后，您的WHERE条件从这两行中删除了一个，我得到了其中一行的instituiton ID是25，而我不想要what。

下面是上面的图像：

• 我的查询是带分页（我在问题标题中添加了“带分页”）
• 我使用的不是@namedquery而是@query
• 我的错误是没有将countQuery参数写入@query注释

Answer 1

我解决了...

查询需要如下所示：

@Query(
        value = "SELECT * FROM \n" +
                "(institution INNER JOIN user ON institution.user_id = user.id) \n" +
                "LEFT JOIN \n" +
                "(SELECT * FROM \n" +
                "building_institutions \n" +
                "WHERE building_id = :userId)\n" +
                " AS reserved_institutions \n" +
                "ON reserved_institutions.institutions_user_id = kits_nwt.institution.user_id \n" +
                " where reserved_institutions.institutions_user_id IS null \n"
                + "ORDER BY ?#{#pageable}"
        ,
        countQuery = "SELECT count(*) FROM \n" +
                "(institution INNER JOIN user ON institution.user_id = user.id) \n" +
                "LEFT JOIN \n" +
                "(SELECT * FROM \n" +
                "building_institutions \n" +
                "WHERE building_id =:userId)\n" +
                " AS reserved_institutions \n" +
                "ON reserved_institutions.institutions_user_id = kits_nwt.institution.user_id \n" +
                "where reserved_institutions.institutions_user_id IS null \n" +
                "ORDER BY ?#{#pageable}",
        nativeQuery = true)
Page<Institution> findPotentialInstitutionsByBuildingId(@Param("userId") Long userId, Pageable pageable);

当我的查询如下所示时，我在第4行靠近WHERE的地方得到了错误：

@Query(
        value = "SELECT username, password, description, location, title, user_id FROM (institution INNER JOIN user ON institution.user_id = user.id) LEFT JOIN\n" +
                "(SELECT * FROM building_institutions WHERE building_institutions.building_id = 1) AS reserved_institutions\n" +
                "ON reserved_institutions.institutions_user_id = kits_nwt.institution.user_id\n" +
                "WHERE reserved_institutions.institutions_user_id IS null ORDER BY ?#{#pageable}",
        nativeQuery = true)
Page<Institution> findPotentialInstitutionsByBuildingId(Long userId, Pageable pageable);

我希望我能让别人免于几个小时的痛苦。

干杯！：）