67,512
社区成员
发帖
与我相关
我的任务
分享
public interface PosAccountRecordRepository extends JpaRepository<PosAccountRecord, String> {
List<PosAccountRecord> findTop3ByposAccountIdAndRecordTypeOrderByRecordTimeDesc(final String posAccountId, final int recordType);
@Query(value="SELECT "
+ "SUM(pos.AMOUNT) sumamount, COUNT(1) sumcount, pos.RECORD_TYPE FROM "
+ "POS_ACCOUNT_RECORD pos "
+ "GROUP BY pos.RECORD_TYPE ",nativeQuery=true)
List<Object[]> findPosAccountCollect();
@Query(value="SELECT "
+ "SUM( IF ( pos.RECORD_TYPE = 0, pos.AMOUNT,0 ) ) AS sumincomeamount, "
+ "SUM( IF ( pos.RECORD_TYPE = 1, pos.AMOUNT,0 ) ) AS sumoutgoamount, "
+ "SUM( IF ( pos.RECORD_TYPE = 0, 1,0 ) ) AS sumincomecount, "
+ "SUM( IF ( pos.RECORD_TYPE = 1, 1,0 ) ) AS sumoutgocount "
+ "FROM POS_ACCOUNT_RECORD pos "
+ "where 1=1 "
+ "and pos.RECORD_TIME <= STR_TO_DATE(:a,'%Y-%m-%d') ",nativeQuery=true)
List<Object[]> findPosAccountSumCollect(@Param(value = "a") String a);
}
写两个方法 一个带where条件 在参数不为空的时候调用,另一个不带where条件,在参数为空的时候调用