mysql - 如何在不使用辅助表的情况下连接两个在查询中应该只有一个的表？ (使用两列进行左连接即可)

Question

我必须使用一个不完美的模型，因为两个不同的表应该只是一个......让我们简化模型并显示它:

Table: Survey
Columns: ID_SURVEY | SURVEY_TYPE | SURVEY_QUESTIONS

Table: SurveySent
Columns: ID_SENT | ID_USER | ID_SURVEY | TMST_SENT

Table: SurveyAnswered
Columns: ID_RECEIVED | ID_USER | ID_SURVEY | TMST_RECEIVED | ANSWERS

-- the first column is the primary key, columns wiht same name are foreign keys

现在，如果我构建了模型 SurveyAnswered 和 SurveySent 将是同一个表，因为仅当首先发送调查时才存在回答的调查，或者至少根据发送的调查来回答调查。

但是情况并非如此，因此我尝试通过单个查询获取结果，希望是高效的查询，因为我无法编写任何内容。

我想要获取的是用户已收到的调查数量、该用户已回答的调查数量，所有这些都是针对一种特定类型的调查。经过多次尝试，我设法做到这一点的唯一方法是连接两个子查询:

SELECT Z.ID_USER, COUNT(DISTINCT ID_SENT), COUNT(DISTINCT ID_RECEIVED) FROM
  (SELECT A1.ID_USER, A1.ID_SENT 
    FROM SurveySent A1 
    JOIN Survey B ON A1.ID_SURVEY = B.ID_SURVEY 
   WHERE B.SURVEY_TYPE = @MyType) AS Z
LEFT JOIN
   (SELECT A2.ID_USER, A2.ID_RECEIVED 
    FROM SurveyAnswered A2 
    JOIN Survey B ON A2.ID_SURVEY = B.ID_SURVEY 
   WHERE B.SURVEY_TYPE = @MyType) AS Y
ON Z.ID_USER = Y.ID_USER
GROUP BY Z.ID_USER

有没有一种方法可以通过单个查询(而不是具有 2 个子查询的查询)来执行此操作？问题的核心似乎是我想同时加入两列:ID_SURVEY 和 ID_USER 但我还没有找到在单个查询中执行此操作的方法...

诸如此类的事情:

SELECT A1.ID_USER, COUNT(DISTINCT ID_SENT), COUNT(DISTINCT ID_RECEIVED)
FROM SurveySent A1
LEFT JOIN SurveyAnswered A2 ON A1.ID_USER=A2.ID_USER
JOIN Survey B ON B.ID_SURVEy = A1.ID_SURVEY
WHERE B.SURVEY_TYPE = @MyType
 AND (A2.ID_SURVEY = B.ID_SURVEY OR A2 IS NULL)
GROUP BY A1.ID_USER

不起作用，因为左连接后空值丢失了......有什么帮助吗？

Answer 1

使用调查表来“驱动”查询。也就是说，将其设为第一个表并使用左外连接。

SELECT s.ID_USER, COUNT(DISTINCT ss.ID_SENT), COUNT(DISTINCT sa.ID_RECEIVED)
FROM Survey s left outer join
     SurveySent ss
     on s.id_survey = ss.id_survey left outer join
     SurveyAnswered sa
     on s.id_survey = sa.id_survey and ss.id_user = sa.id_user
WHERE B.SURVEY_TYPE = @MyType;