mysql - 连接子查询时使用if条件

Question

我需要在以下查询中，如果 Events.Type 字段等于 1，则带有 XXXXX 的查询部分应如下所示:

ON Stages.ID = Abandonments.IDstage

如果是 2，Events.Type 中带有 XXXXX 的查询部分如下所示:

ON Stages.ID > Abandonments.IDstage

查询

SELECT
*,
  Events.Type
FROM Participants
  INNER JOIN Times
    ON Participants.ID = Times.IDparticipant
  INNER JOIN Events
    ON Events.ID = Participants.IDevent
    AND Events.ID = Times.IDevent
  INNER JOIN Stages
    ON Events.ID = Stages.IDevent
    AND Stages.ID = Times.IDstage
  LEFT OUTER JOIN (SELECT
      Retired.ID,
      Retired.IDstage,
      Retired.IDparticipant,
      Retired.IDevent
    FROM Retired) Abandonments
    XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
    AND Participants.ID = Abandonments.IDparticipant
    AND Events.ID = Abandonments.IDevent
WHERE Times.IDevent = 31
AND Times.IDstage = 156
ORDER BY Participants.ID

谢谢

Answer 1

您的 SQL 查询中有很多 X ，并且不显示您正在尝试构建的限定符，所以我不太知道您在这里试图达到的意图。

但是，您在问题中描述了解决方案。

因此:

ON ((Stages.ID = Abandonments.IDstage AND Events.Type = 1) OR 
   (Stages.ID > Abandonments.IDstage AND Events.Type = 2))

如果这些定义的参数都不为 true(例如 Events.Type = 3)，那么您需要添加限定符或 false 返回来排除整个 JOIN

ON ((Stages.ID = Abandonments.IDstage AND Events.Type = 1) OR 
   (Stages.ID > Abandonments.IDstage AND Events.Type = 2)) OR 2=1