MySQL如何根据条件动态选择字段关联users和swipes两张表

方案1：SQL层面直接实现（推荐） #

你可以两次关联users表，分别拿到滑动发起方和接收方的用户名，再通过条件判断取对应字段即可，同时注意你原代码里的swpipes是拼写错误，已修正：

SELECT 
  CASE
    -- 满足第一个条件时取swp_to对应的用户名
    WHEN (swipes.swp_from = :me AND ((swipes.first_swp NOT IN ('like', 'superlike')) OR (swipes.first_swp IN ('like', 'superlike') AND swipes.second_swp NOT IN ('like', 'superlike')))) THEN u_to.name
    -- 满足第二个条件时取swp_from对应的用户名
    WHEN (swipes.swp_to = :me AND swipes.second_swp != 'pending') THEN u_from.name
  END AS name,
  swp_to,
  swp_from,
  first_swp,
  second_swp
FROM swipes
-- 分别关联两次users表，前缀区分接收方、发起方
LEFT JOIN users u_to ON u_to.id = swipes.swp_to
LEFT JOIN users u_from ON u_from.id = swipes.swp_from
WHERE 
  (swipes.swp_from = :me AND ((swipes.first_swp NOT IN ('like', 'superlike')) OR (swipes.first_swp IN ('like', 'superlike') AND swipes.second_swp NOT IN ('like', 'superlike'))))
  OR
  (swipes.swp_to = :me AND swipes.second_swp != 'pending')

只要users表的id是主键，两次关联的性能损耗可以忽略，不需要修改原有查询逻辑。

方案2：PHP层面处理 #

如果需要保留更多原始字段做后续逻辑，可以先把两个关联用户的信息都查出来，遍历的时候逐行判断取值即可，示例代码（PDO为例）：

// 执行查询
$stmt = $pdo->prepare("
  SELECT 
    u_to.name as to_name,
    u_from.name as from_name,
    swp_from,
    swp_to,
    first_swp,
    second_swp
  FROM swipes
  LEFT JOIN users u_to ON u_to.id = swipes.swp_to
  LEFT JOIN users u_from ON u_from.id = swipes.swp_from
  WHERE 
    (swipes.swp_from = :me AND ((swipes.first_swp NOT IN ('like', 'superlike')) OR (swipes.first_swp IN ('like', 'superlike') AND swipes.second_swp NOT IN ('like', 'superlike'))))
    OR
    (swipes.swp_to = :me AND swipes.second_swp != 'pending')
");
$stmt->bindValue(':me', $currentUserId, PDO::PARAM_INT);
$stmt->execute();
$rawList = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 遍历处理结果
$processedList = [];
foreach ($rawList as $item) {
    $cond1 = ($item['swp_from'] == $currentUserId) && (($item['first_swp'] != 'like' && $item['first_swp'] != 'superlike') || (($item['first_swp'] == 'like' || $item['first_swp'] == 'superlike') && ($item['second_swp'] != 'like' && $item['second_swp'] != 'superlike')));
    $cond2 = ($item['swp_to'] == $currentUserId) && $item['second_swp'] != 'pending';
    $item['name'] = $cond1 ? $item['to_name'] : $item['from_name'];
    // 清理冗余字段
    unset($item['to_name'], $item['from_name']);
    $processedList[] = $item;
}

内容的提问来源于stack exchange，提问作者Relaxing Music