时间戳

首页 > 解决方案 > 选择连接两个表并循环以获得两个不同的值

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

问题描述

我需要查询 RELATIONS 表（两个日期之间的 WHERE）并获取 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

欢迎任何关于这个问题的更好标题的想法。

标签： mysqlsql

解决方案

我认为你只需要两个JOINs：

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

推荐阅读