如何用JPA在spring-boot中对聚合列进行排序来进行SQL查询？

Question

mysql> desc t_product_purchase;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| oid             | bigint(20)   | NO   | PRI | NULL    |       |
| consumer_id     | bigint(20)   | NO   |     | NULL    |       |
| number          | bigint(20)   | YES  |     | NULL    |       |
| pay_time        | datetime     | YES  |     | NULL    |       |
| payment         | float        | YES  |     | NULL    |       |
| price           | float        | YES  |     | NULL    |       |
| product_num_iid | bigint(20)   | NO   |     | NULL    |       |
| seller_id       | bigint(20)   | NO   | MUL | NULL    |       |
| product_title   | varchar(255) | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
9 rows in set (0.00 sec)

select m.product_num_iid, sum(m.payment) as payment, sum(m.number) as nbr from t_product_purchase m where m.seller_id = 247475251 and m.pay_time >= 0  group by m.product_num_iid order by payment desc limit 0, 10

@Query(value = "select m.product_num_iid, sum(m.payment) as payment, sum(m.number) as nbr from t_product_purchase m where m.seller_id = ?1 and m.pay_time >= ?2 and m.pay_time < ?3 group by m.product_num_iid order by ?4 desc limit ?5, ?6", nativeQuery = true)
public List findRankedProductPaymentOrCountBySellerIdWithinPayTime(long sellerId, Date startPayTime, Date endPayTime, String orderBy, long offset, long size);

有人知道为什么repository方法没有按照预期工作吗？多谢了。

Answer 1

不支持，输入参数仅允许在WHERE和HAVING中使用，不能使用ORDER BY子句的块和参数。

解决方案

order by 
case when 'a' = :a then ec.ISACTIVE else 1  end asc,
case when 'a' != :a then ec.ISACTIVE else 1  end desc

以下解决方案较好：

null