mysql - 连接同一个表三次并从中检索一列

Question

SELECT DISTINCT 
  ,PH.PHONE_TYPE_CD
  ,PH.PHONE_NUM
FROM PERSON P
LEFT JOIN PHONE PH
   ON PH.PARENT_ENTITY_ID         = P.PERSON_ID
  AND PH.PARENT_ENTITY_NAME       = 'PERSON'
  AND PH.PHONE_TYPE_CD            = ?
  AND PH.ACTIVE_IND               = 1
LEFT JOIN PHONE PH
   ON PH.PARENT_ENTITY_ID         = P.PERSON_ID
  AND PH.PARENT_ENTITY_NAME       = 'PERSON'
  AND PH.PHONE_TYPE_CD            = ?
  AND PH.ACTIVE_IND               = 1
LEFT JOIN PHONE PH
   ON PH.PARENT_ENTITY_ID         = P.PERSON_ID
  AND PH.PARENT_ENTITY_NAME       = 'PERSON'
  AND PH.PHONE_TYPE_CD            = ?
  AND PH.ACTIVE_IND               = 1

此处 PHONE_TYPE_CD 将从 Java 端传递，基于 PHONE_TYPE_CD，查询应运行并返回结果。

由于我是 SQL 新手，所以我不知道如何实现这一点。我知道所有 3 个连接都应该有别名，如 PHONE PH1、PHONE PH2 等。我的问题是我可以像下面这样编码并根据传递的内容获取 PHONE_NUM PHONE_TYPE_CD:

SELECT DISTINCT 
  ,PH1.PHONE_TYPE_CD
  ,PH1.PHONE_NUM
  ,PH2.PHONE_TYPE_CD
  ,PH2.PHONE_NUM
  ,PH3.PHONE_TYPE_CD
  ,PH3.PHONE_NUM
  FROM PERSON P
LEFT JOIN PHONE PH1
   ON PH1.PARENT_ENTITY_ID         = P.PERSON_ID
  AND PH1.PARENT_ENTITY_NAME       = 'PERSON'
  AND PH1.PHONE_TYPE_CD            = ?
  AND PH1.ACTIVE_IND               = 1
LEFT JOIN PHONE PH2
   ON PH2.PARENT_ENTITY_ID         = P.PERSON_ID
  AND PH2.PARENT_ENTITY_NAME       = 'PERSON'
  AND PH2.PHONE_TYPE_CD            = ?
  AND PH2.ACTIVE_IND               = 1
LEFT JOIN PHONE PH3
   ON PH3.PARENT_ENTITY_ID         = P.PERSON_ID
  AND PH3.PARENT_ENTITY_NAME       = 'PERSON'
  AND PH3.PHONE_TYPE_CD            = ?
  AND PH.ACTIVE_IND                = 1

我对检索部分有歧义。

Answer 1

你可以这样做:

SELECT DISTINCT 
  ,PH1.PHONE_TYPE_CD
  ,PH1.PHONE_NUM
  ,PH2.PHONE_TYPE_CD
  ,PH2.PHONE_NUM
  ,PH3.PHONE_TYPE_CD
  ,PH3.PHONE_NUM
  FROM PERSON P
LEFT JOIN PHONE PH1
   ON PH1.PARENT_ENTITY_ID         = P.PERSON_ID
  AND PH1.PARENT_ENTITY_NAME       = 'PERSON'
  AND PH1.PHONE_TYPE_CD            in (type1, type2, type3)
  AND PH1.ACTIVE_IND               = 1