php - 选择其中 column1 = column2

Question

在过去的几个小时里，我一直在尝试完成以下操作，但没有成功:

$stmt = $db->query( "SELECT league_match_id as match_id, league_match_home_team as home_team, league_match_away_team as away_team FROM $table WHERE (( home_team = away_team ) AND (away_team = home_team))" );

假设我们有 team1 和 team2。在 Team1 主场和 Team2 客场的比赛中进行。另一场比赛(行)存储在 team2 主场和 team1 客场的位置。我想用一个查询选择两个团队。

没有球队在与自己比赛，我试图获得 2 行，其中反射(reflect)了 home_team 和 away_team 的值。

谁能帮助我走上正轨？

* 更新 *

我得到的返回如下:

Array
(
   [0] => Array
    (
        [t1_id] => 26
        [t1_home] => 2
        [t1_away] => 1
        [t2_id] => 24
        [t2_home] => 1
        [t2_away] => 2
    )

   [1] => Array
    (
        [t1_id] => 28
        [t1_home] => 3
        [t1_away] => 1
        [t2_id] => 25
        [t2_home] => 1
        [t2_away] => 3
    )

   [2] => Array
    (
        [t1_id] => 24
        [t1_home] => 1
        [t1_away] => 2
        [t2_id] => 26
        [t2_home] => 2
        [t2_away] => 1
    )

   [3] => Array
    (
        [t1_id] => 29
        [t1_home] => 3
        [t1_away] => 2
        [t2_id] => 27
        [t2_home] => 2
        [t2_away] => 3
    )

   [4] => Array
    (
        [t1_id] => 25
        [t1_home] => 1
        [t1_away] => 3
        [t2_id] => 28
        [t2_home] => 3
        [t2_away] => 1
    )

   [5] => Array
    (
        [t1_id] => 27
        [t1_home] => 2
        [t1_away] => 3
        [t2_id] => 29
        [t2_home] => 3
        [t2_away] => 2
    )

)

Array[0] 和 Array[2] 相同的地方，它们只是镜像。我可以去掉这里的重复项吗？我希望只有 Array[0] 或 Array[2]。这可能吗？

Answer 1

我怀疑是否会有“主队”与“客队”相同的行。

听起来好像您想找到匹配的两行。

根据您的查询条件，听起来您可能想要这样的东西:

SELECT t1.league_match_id         AS t1_match_id
     , t1.league_match_home_team  AS t1_home_team
     , t1.league_match_away_team  AS t1_away_team
     , t2.league_match_id         AS t2_match_id
     , t2.league_match_home_team  AS t2_home_team
     , t2.league_match_away_team  AS t2_away_team
  FROM $table t1
  JOIN $table t2
    ON t1.league_match_home_team = t2.league_match_away_team
   AND t1.league_match_away_team = t2.league_match_home_team

这假设您在表格中有相应的行，例如

 id  home   away
 --  -----  ------
  2  bears  tigers
  3  tigers bears

如果有多行具有相同的 (home,away)，您将获得多个匹配项。例如，与:

 id  home   away
 --  -----  ------
  2  bears  tigers
  3  tigers bears
  5  tigers bears
  7  tigers bears
 11  bears  tigers

您总共会得到十二行。 (id 值为 2 和 11 的行将分别与 id 值为 3、5 和 7 的行“匹配”。)

---

更新

删除重复项取决于重复项的来源。添加 DISTINCT 关键字将确保结果集中没有两行完全相同，但我怀疑您的重复问题比这更深...... bears 和老虎在多场联赛中主客场交手。

在那种情况下，您需要在表中添加一些额外的内容，并需要一些谓词来限制匹配。这可能是日期，以及一些获取“最新日期”的方法，但这取决于表中的其他内容。

仅显示列，GROUP BY 和聚合函数(如 MAX())可用于为每个“匹配项”获取一个不同的行。

例如:

SELECT MAX(t1.league_match_id)    AS t1_match_id
     , t1.league_match_home_team  AS t1_home_team
     , t1.league_match_away_team  AS t1_away_team
     , MAX(t2.league_match_id)    AS t2_match_id
     , t1.league_match_away_team  AS t2_home_team
     , t1.league_match_home_team  AS t2_away_team
  FROM $table t1
  JOIN $table t2
    ON t1.league_match_home_team = t2.league_match_away_team
   AND t1.league_match_away_team = t2.league_match_home_team
 GROUP 
    BY t1.league_match_home_team
     , t1.league_match_away_team

请注意，从 t2 返回 home 和 away 是多余的，因为 t1.home = t2.away 等等。来自 t1 和 t2 是一样的，除了 home 和 away 交换了。

为了限制“反向”行，所以你会得到 (bears,tigers) 而不是 (tigers,bears)，你可以指定一个额外的谓词，这样你只能得到逆的一个“边”:

AND t1.league_match_home_team < t2.league_match_home_team

---

跟进

(我的查询中有一个拼写错误，第一个 JOIN 谓词应该在右侧指定 t2.。我相信 OP 发现了这个问题并修复了它。)

根据最新更新，要消除结果集中的“镜像”反向行，您可以添加这样的谓词(如果您的查询有一个谓词，则在 GROUP BY 子句之后。)

  HAVING t1_id < t2_id

(HAVING 子句可以引用分配给返回列的别名，这与 WHERE 子句不同。)

如果您的查询中没有 GROUP BY，您可能会使用 WHERE 子句获得更好的性能:

WHERE t1.match_id < t2.match_id

如果获取两行中的哪一行并不重要，那么无论是小于还是大于比较都无关紧要。您选择比较 t1 和 t2 中的哪一列(“id”、“home”或“away”)并不重要，只需比较 t1 和 t2 之间保证不同的列(所以你只会看到镜子的一侧。)