在“jpa query”中，当column的param为null时，如何不按列分组？

e0uiprwp 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(320)

我要从表中选择数据，并按city\u id、district\u id、ward\u id分组。如果其中一列的have param为null，则按其余列分组。
示例：如果param:ward\u id为空，则groupby列city\u id、district\u id.和column ward\u id不影响groupby。谢谢。
编辑：我在查询中添加sum（人）和filter year。

@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
    )

Java sql spring-data-jpa jpa

来源：https://stackoverflow.com/questions/63276360/how-to-not-group-by-column-when-param-of-column-is-null-in-jpa-query

1条答案

按热度按时间

bmp9r5qi1#

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 )

您还将根据这3列筛选数据。
所以答案是把这群人全部除名。如果要控制输出的顺序，请改用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

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

赞(0）回复(0）举报 2021-07-24

我来回答

在“jpa query”中，当column的param为null时，如何不按列分组？

1条答案

相关问题

热门标签

最新问答