java - jpa中求和查询怎么写？

Question

我在 mySql 中有一个查询，我想在我的 Controller 中写入它。

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`

这相当于 jpa

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

我期望与 phpmyadmin 结果相同的结果，但我收到此错误:

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '=' near line 1, column 23 [SELECT SUM(column='value') ,b.date FROM com.project.Myclass b WHERE b.user= :user and b.date between :dt1 and :dt2 group by b.date] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:188) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final] at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:729) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final] at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23) ~[hibernate-core-5.3.10.Final.jar:5.3.10.Final] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_161] at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_161] at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) ~[na:1.8.0_161] at java.lang.reflect.Method.invoke(Unknown Source) ~[na:1.8.0_161] at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:350) ~[spring-orm-5.1.8.RELEASE.jar:5.1.8.RELEASE] at com.sun.proxy.$Proxy118.createQuery(Unknown Source) ~[na:na] at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:87) ~[spring-data-jpa-2.1.9.RELEASE.jar:2.1.9.RELEASE] ... 76 common frames omitted

Answer 1

将 boolean 表达式替换为 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 似乎不支持。