英文:
Order by date not working with distinct using native query
问题
目前，我有一个存储库，在这个存储库中，我正在使用本机查询来查找前10个唯一的ID，并且这些ID应该按最近的顺序获取。为此，我在我的存储库类中使用以下查询：
@Repository
public interface HomePageRepository extends JpaRepository<TransferEntity, Integer> {
@Query(value="select DISTINCT transfer_to from transfers_table where transfer_from= :transfer_from ORDER BY transaction_date DESC LIMIT 10;", nativeQuery=true)
public ArrayList<Integer> getTransferRequests(Integer transfer_from);
}
以下是我正在使用的实体类：
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "transfers_table")
public class TransferEntity {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Integer Id;
private Integer transfer_from;
private Integer transfer_to;
private Double transaction_amount;
@Column(nullable = false, updatable = false)
@CreationTimestamp
private Date transaction_date;
}
现在，当我尝试运行它时，它给我返回以下错误：
java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column 'db.transfers_table.transaction_date' which is not in SELECT list; this is incompatible with DISTINCT
当我从语句中删除Order by子句时，同样的查询可以工作，但是我无法首先获取最新的ID。
英文:
Currently I have a repository where I'm using a native query to find out the first 10 unique ids and these ids should be fetched with the recent ones first. To do this I'm using the following query in my repository class:
@Repository
public interface HomePageRepository extends JpaRepository<TransferEntity, Integer> {
@Query(value="select DISTINCT transfer_to from transfers_table where transfer_from= :transfer_from ORDER BY transaction_date DESC LIMIT 10;", nativeQuery=true)
public ArrayList<Integer> getTransferRequests(Integer transfer_from);
}
The following is my entity class I'm using:
@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@Table(name = "transfers_table")
public class TransferEntity {
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Integer Id;
private Integer transfer_from;
private Integer transfer_to;
private Double transaction_amount;
@Column(nullable = false, updatable = false)
@CreationTimestamp
private Date transaction_date;
}
Now this is giving me the following error when I try to run it:
> java.sql.SQLException: Expression #1 of ORDER BY clause is not in SELECT list, references column 'db.transfers_table.transaction_date' which is not in SELECT list; this is incompatible with DISTINCT
This same query works when I remove the Order by clause from the statement, but then I cannot fetch the recent ids first.
答案1
得分: 0
使用子查询：
从 (
选择 DISTINCT 转账至 from (
选择 转账至, 交易日期 from 转账表 where 转账自= 6 按交易日期降序限制 10
) 作为 x
英文:
use subquqries:
select DISTINCT transfer_to from (
select transfer_to, transaction_date from transfers_table where transfer_from= 6 ORDER BY transaction_date DESC LIMIT 10
) as x
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论