java“无法按查询提取JPQL组中的结果集”
在使用Spring Data JPA的Spring Boot 1.5应用程序中,我有一个TDR
表,如下所示:
Table TDR
-----------------------
id | c_id | m_id | p_id
1 | 1 | 1 | 1
2 | 1 | 1 | 2
3 | 2 | 1 | 1
4 | 2 | 1 | 2
5 | 3 | 1 | 1
在这里,我需要找到按m_id
和p_id
分组的最后记录。对于PostgreSQL,我使用MAX
聚合函数来实现这一点:
SELECT t.id, t.c_id, t.m_id, t.p_id
FROM (
SELECT MAX(id) AS id, m_id, p_id
FROM tdr
WHERE m_id = 1 -- <<< parameterized below
GROUP BY m_id, p_id
) m JOIN tdr t ON t.id = m.id
返回正确的记录:
Table TDR
-----------------------
id | c_id | m_id | p_id
4 | 2 | 1 | 2
5 | 3 | 1 | 1
由于GROUP BY
不包括c_id
,因此SQL的结构是correlated sub-query,并与联接匹配(有关详细信息,请参见此question)
我想用这个Lombok ^{
@Data
@Entity
@Table(name = "TDR")
public class TDR implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(nullable = false)
private Long id;
@ManyToOne(optional=false, cascade = { CascadeType.ALL })
@JoinColumn(name="m_id")
private M m;
@ManyToOne(optional=false, cascade = { CascadeType.ALL })
@JoinColumn(name="p_id")
private P p;
@ManyToOne(optional=false, cascade = { CascadeType.ALL })
@JoinColumn(name="c_id")
private C c;
}
但是我的JPQL用于参数化的m ID(:m_id
)的相关子查询:
SELECT t FROM TDR t WHERE t.id in (
SELECT MAX(t.id) AS id, t.m, t.p FROM TDR t WHERE t.m = :m GROUP BY t.m, t.p
)
导致此错误的原因:
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
// at ...
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
// at ...
Caused by: org.postgresql.util.PSQLException: ERROR: subquery has too many columns
# 1 楼答案
我首先想到的是你的第一个选择:
SELECT FROM
。。。不说明要选择的列
# 2 楼答案
仅选择相关子查询中的联接列:
这个ObjectDB GROUP BY article和这个question一样有用