java当“JPA查询”中的列参数为空时，如何不按列分组？

Question

我希望从表中选择数据，并使用city_id、district_id、ward_id对数据进行分组。如果其中一列的have param为null，则按其余列进行分组

例如： 如果param:ward_id为空，则按列分组city_id、district_id.和column ward_id不影响分组方式。 谢谢

编辑：我在查询中添加总和（人）并过滤年份

@Query(value = "SELECT city_id, district_id, ward_id, sum(people) as sum_people
        FROM table  
        WHERE (:city_id is null OR city_id =:city_id ) 
        AND (:district_id is null OR district_id =:district_id ) 
        AND (:ward_id is null OR ward_id =:ward_id ) 
        AND year =:year
        GROUP BY city_id, district_id, ward_id",
        nativeQuery = true)
List<Object> findAddress(
        @Param("city_id") Integer city_id,
        @Param("district_id") Integer district_id,
        @Param("ward_id") Integer ward_id,
        @Param("year") Integer year
    )

Answer 1

SELECT * FROM table

您没有执行任何聚合

WHERE (:city_id is null OR city_id =:city_id ) 
AND (:district_id is null OR district_id =:district_id ) 
AND (:ward_id is null OR ward_id =:ward_id ) 

您还可以根据这三列过滤数据

因此，答案是把这群人全部除名。 如果要控制输出的顺序，请使用ORDER BY

---

编辑后，您将sum（人）包括在内

试试下面的方法——注意我还没有测试过

SELECT SELECT city_id, district_id, ward_id, sum(people) as sum_people
FROM (SELECT city_id, district_id, case :ward_id is null then '' else ward_id end ward_id, people
    FROM table
    WHERE (:city_id is null OR city_id =:city_id ) 
    AND (:district_id is null OR district_id =:district_id ) 
    AND (:ward_id is null OR ward_id =:ward_id )
) tmp
GROUP BY city_id, district_id, ward_id END

本质上，当您使用空值筛选病房id时，它会将结果替换为“”，以便group by将聚合所有病房id