使用sql查询中的联接检索数据并在jdbc模板中重新表示

Question

我有三张桌子：

REPOTRANSSMISSION表列是

REPO_TRANSMISSION_ID,
G3_SESSION_ID,
CLIENT_NM,
ASSESSMENT_SESSION_ID,
PACKAGE_SESSION_ID,
TEST_SESSION_ID,
SCORE_SESSION_ID,
REPO_TRANSMISSION_STATE_CD,
REPO_TRANSMISSION_DATA_TX,
REPO_TRANSMISSION_LEVEL_CD,
CREATE_DT,
LAST_MODIFIED_DT. 

这里，REPO_TRANSMISSION_ID是主键，REPO_TRANSMISSION_STATE_CD是预键。

第二表REPO_ TRANSSMISSION_REQ_LOG列是

REPO_TRANSMISSION_REQ_LOG_ID
REPO_TRANSMISSION_ID
REQUEST_TX
RESPONSE_TX
ERROR_TX
CREATE_DT
LAST_MODIFIED_DT 

这里PK_REPO_TRANSMISSION_REQ_LOG是主键，REPO_TRANSMISSION_ID是预键

第3表REPO TRANSSMISSION STATE列为

REPO_TRANSMISSION_STATE_CD
REPO_TRANSMISSION_STATE_DS
CREATE_DT
LAST_MODIFIED_DT
and 
REPO_TRANSSMISSION_STATE_CD values are TRANS_RESP,
RECON_REQ,
RECON_ERR,
RECON_RETRY,
RECON_RESP 

这里，PK_REPO_TRANSMISSION_STATE_cd是主键

当repo_transsmission_Id值高于4时，我必须检索repotransmission_state_cd，并且必须加入第1和第2表。

我将如何编写sql查询？

Answer 1

您只想看看查询将如何给出所需的结果吗？

会是这样的：

SELECT tr.repo_transmission_id
  FROM REPOTRANSSMISSION tr
    JOIN REPO_TRANSSMISSION_REQ_LOG lg ON (tr.REPO_TRANSMISSION_ID = lg.REPO_TRANSMISSION_ID)
WHERE tr.REPO_TRANSMISSION_STATE_CD > 4;