mysql - 选择联接两个表并循环以获取两个不同的值

我需要查询RELATIONS表（两个日期之间的位置），并获取RELATIONS表中每个SOURCE / ACCOUNT对的ENTITY_ID。

- ENTITIES table
ENTITY_ID (PK)
ENTITY_NAME

    - ACCOUNTS table
    SOURCE    (PK)
    ACCOUNT   (PK)
    ENTITY_ID (FK)

        - RELATIONS table
        RELATION_ID (PK)
        SOURCE_1    (FK)
        ACCOUNT_1   (FK)
        SOURCE_2    (FK)
        ACCOUNT_2   (FK)
        TIMESTAMP

有没有办法在一个查询中做到这一点？

查询的输出应如下所示：

RELATION_ID
SOURCE_1
ACCOUNT_1
ENTITY_ID_1 (ENTITY_ID (from ACCOUNTS table) related to SOURCE_1 and ACCOUNT_1)
SOURCE_2
ACCOUNT_2
ENTITY_ID_2 (ENTITY_ID (from ACCOUNTS table) related to SOURCE_2 and ACCOUNT_2)

我对如何获取ENTITY_ID_1有一个想法，但不确定如何同时获取ENTITY_ID_2。

SELECT
     R.RELATION_ID
    ,R.SOURCE_1
    ,R.ACCOUNT_1
    ,A.ENTITY_ID AS ENTITY_ID_1
    ,R.SOURCE_2
    ,R.ACCOUNT_2
FROM RELATIONS R
JOIN ACCOUNTS A
  ON R.SOURCE_1  = A.SOURCE
 AND R.ACCOUNT_1 = A.ACCOUNT
WHERE R.TIMESTAMP >= DATETIME1 AND R.TIMESTAMP < DATETIME2

欢迎对这个问题有更好的标题的任何想法。

最佳答案

我认为您只需要两个JOIN：

SELECT R.RELATION_ID, R.SOURCE_1, R.ACCOUNT_1,
       A1.ENTITY_ID AS ENTITY_ID_1,
       A2.ENTITY_ID AS ENTITY_ID_2,
       R.SOURCE_2, R.ACCOUNT_2
FROM RELATIONS R JOIN
     ACCOUNTS A1
     ON R.SOURCE_1  = A1.SOURCE AND
        R.ACCOUNT_1 = A1.ACCOUNT JOIN
     ACCOUNT A2
     ON R.SOURCE_2  = A2.SOURCE AND
        R.ACCOUNT_2 = A2.ACCOUNT
WHERE R.TIMESTAMP >= DATETIME1 AND
      R.TIMESTAMP < DATETIME2

关于mysql - 选择联接两个表并循环以获取两个不同的值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54554411/