如何在JPA中编写求和查询？

Question

我在mySql中有一个查询，我想在我的控制器中编写。

SELECT sum(`column`='value') ,b.`date`  FROM table b WHERE b.user= 'user1' and b.`date` between '2019-07-21' and '2019-07-26' group by b.`date`

@Transactional
@Modifying
@Query("SELECT sum(column='value') ,b.date  FROM myclasse b WHERE b.user= :user and b.date between :dt1 and :dt2 group by b.date")
    List<Object[]> function( String user,@Param("dt1")Date dt1,@Param("dt2")Date dt2);

原因：java.lang.IllegalArgumentException：org.hibernate.hql.internal.ast.QuerySyntaxException：应为CLOSE，在org.hibernate.internal.exceptionConverterImpl.convert（exceptionConverterImpl.java:138)~[hibernate-core.exceptionConverterImpl.java:181)~[hibernate-core][SELECT SUM(column='value')，b.date FROM com.project.myclass b，其中b.user=:user和b.date by by by date]第1行第23列附近发现“=”[SELECT SUM（column='value')，b.date]-5.3.10.final.jar:5.3.10.final]在org.hibernate.internal.exceptionconverterimpl.convert（exceptionconverterimpl.java:188)~[hibernate-core-5.3.10.final.jar:5.3.10.final]在org.hibernate.internal.abstractsharedsessioncontract.createquery（abstractsharedsessioncontract.createquery（abstractsharedsessioncontract.java:729)~[hibernate-core-5.3.10.final]在org.hibernate.internal.abstractsessioncontract.java:23)~[hibernate-core-5.3.10.final.jar:5.3.10.final]在al.jar:5.3.10.final]在sun.refleCT.NativeMethodAccessorImpl.Invoke0（本机方法）~[NA:1.8.0_161]在Sun.Reflect.NativeMethodAccessorImpl.Invoke（未知源）~[NA:1.8.0_161]在Sun.Reflect.DelegatingMethodAccessorImpl.Invoke（未知源）~[NA:1.8.0_161]在java.lang.Reflect.Method.Invoke（未知源）~[NA:1.8.0_161]在org.SpringFramework.orm.jpa.ExtendeDentityManagerCreator ar:5.1.8.release]在com.sun.proxy.$proxy118.createquery（未知源）~[na:na]在org.springframework.data.jpa.repository.query.simplejpaquery.validatequery（simplejpaquery.java:87)~[spring-data-jpa-2.1.9.release.jar:2.1.9.release]...省略76个常见帧

Answer 1

用case表达式替换布尔表达式：

SELECT
    b.date,
    COUNT(CASE WHEN some_col = 'some_value' THEN 1 END) AS cnt
FROM table_b b
WHERE b.user = 'user1' AND b.date BETWEEN '2019-07-21' AND '2019-07-26' 
GROUP BY b.date;

语法sum(column='value')是MySQL（可能还有一些其他数据库，如Postgres)，但似乎不受JPA支持。