java - 如何在JPA JPQL中编写这个SQL select？

Question

我有一个表，将其命名为REI_BODY，我还有另一个表，将其命名为REI_AMT_INFO。我在 oracle pl/sql 中有一个选择:

SELECT *,

  (SELECT AMT    FROM REI_AMT_INFO I WHERE I.REI_ID=T.REI_ID AND AMT_TYPE_CD = '01') AMT_01,
  (SELECT AMT    FROM REI_AMT_INFO I WHERE I.REI_ID=T.REI_ID AND AMT_TYPE_CD = '02') AMT_02,

  (SELECT SGN_CD FROM REI_AMT_INFO I WHERE I.REI_ID=T.REI_ID AND AMT_TYPE_CD = '01') SGN_01,
  (SELECT SGN_CD FROM REI_AMT_INFO I WHERE I.REI_ID=T.REI_ID AND AMT_TYPE_CD = '02') SGN_02,

FROM REI_BODY T

这是 REI_AMT_INFO 表，没有外键!:

REI_ID (Number);
AMT_TYPE_CD (Varchar);
AMT (Number);
SGN_CD (Varchar);

我必须使用 JPA JPQL 语言创建此选择。我怎样才能做到这一点？

Answer 1

@NamedQuery(
    name="REI_BODY",
    query="SELECT *,

  (SELECT AMT    FROM REI_AMT_INFO I WHERE I.REI_ID=T.REI_ID AND AMT_TYPE_CD = '01') AMT_01,
  (SELECT AMT    FROM REI_AMT_INFO I WHERE I.REI_ID=T.REI_ID AND AMT_TYPE_CD = '02') AMT_02,

  (SELECT SGN_CD FROM REI_AMT_INFO I WHERE I.REI_ID=T.REI_ID AND AMT_TYPE_CD = '01') SGN_01,
  (SELECT SGN_CD FROM REI_AMT_INFO I WHERE I.REI_ID=T.REI_ID AND AMT_TYPE_CD = '02') SGN_02,

FROM REI_BODY T"
)

CHECK THE LINK