我使用spring数据jpa。
实际上,我有三张table。
public class LE
@Id
private Long eventId;
@ManyToOne
private PR pr;
...
}
public class PR
@Id
private Long prId;
@OneToMany(mappedBy="pr")
private PRBA prba;
@OneToMany(mappedBy="pr")
private List<LE> le;
...
}
public class PRBA{
@Id
private Long prbaId;
@ManyToOne
private PR pr;
@ManyToOne
private BA ba;
private LocalDate date;
}
public class BA{
@Id
private Long baId;
@OneToMany(mappedBy="ba")
private List<PRBA> prba;
}
我编写了这个jpql查询
select count(le) from LE le
inner join le.pr pr
inner join p.prba prba
inner join p.prba prba2
where
pr.prId=1
and prba.baId!=prba2.baId
我搜索得到的人数le谁有相同的价值为公关,但不同的价值为ba
我有这个数据库值
LE table
eventId prId
1 1
2 1
PR table
prId
1
2
PRBA table
prbaId prId baId
1 1 1
2 1 2
BA table
baId
1
2
似乎查询不好,因为它返回4
好像是笛卡尔问题
我在等2个
hibernate生成的sql是
select
count(le.event_id) as col_0_0_
from
le le0
inner join
pr pr1
on le0.pr_id=pr1.pr_id
inner join
prba prba1
on pr1.pr_id=prba1.pr_id
inner join
prba prba2
on pr1.pr_id=prba2.pr_id
where
le0.pr_id=?
and prba1.ba_id<>prba2.ba_id
1条答案
按热度按时间q0qdq0h21#
SELECT COUNT(DISTINCT le) ...
应该会成功的。请参见:https://stackoverflow.com/a/5339434/66686