php - 从不同列中选择具有相同值的行

Question

假设我有一个数据库，用于存储我和 friend 最近的足球比赛的数据。我会在每一行中存储我 friend 的名字，没有特定的顺序。我如何才能在不必遍历每一行以查找每一列中的每个值的情况下提取数据？

这是一个示例表:

gameID   player1     player2    player3   .......
1        John        Chance     Gordon
2        Jim         Gordon     John
3        Derek       Chance     Richard

如果 John 在任何列中，我如何选择他玩过的行？我打算将它们放入一个 PHP 数组中并计算它们的出现次数。在不做类似的情况下检查每一列的最有效方法是什么:

$playerarray=array{'Derek','John','Gordon','Richard','Jim','Chance',....}
foreach($playerarray as $key => $value){
    //Is this even horribly inefficient?
    $sql = mysql_query(SELECT * from game_table WHERE player1='$value' OR player2='$value' OR player3='$value'...)
}

Answer 1

你需要的SQL语句如下:

SELECT * FROM game_table WHERE CONCAT_WS(',', player1, player2, player3) LIKE '%John%'

此查询将返回John所在行的所有字段:首先将3个玩家的列用逗号连接，然后搜索John是否在其中。

如果你只想数数:

SELECT COUNT(DISTINCT gameID) as numberofmatches FROM game_table WHERE CONCAT_WS(',', player1, player2, player3) LIKE '%,John,%' OR CONCAT_WS(',', player1, player2, player3) LIKE 'John,%' OR CONCAT_WS(',', player1, player2, player3) LIKE '%,John'

或者如果你想要所有玩家的列表:

(SELECT DISTINCT player1 AS player FROM game_table) UNION
(SELECT DISTINCT player2 AS player FROM game_table) UNION
(SELECT DISTINCT player3 AS player  FROM game_table)
ORDER BY player

你也可以把这个和前面的结合起来，这给了我你想要的(我认为):

SELECT player,count(distinct gameID) as numberofgames FROM game_table t1 JOIN ((SELECT DISTINCT player1 AS player FROM game_table) UNION (SELECT DISTINCT player2 AS player FROM game_table) UNION (SELECT DISTINCT player3 AS player  FROM game_table) ORDER BY player) t2 WHERE t2.player=t1.player1 OR t2.player=t1.player2 OR t2.player=t1.player3 GROUP by t2.player

这会返回一个表格，其中包含所有不同玩家的姓名，包括他们玩过的游戏数量!

如果您只想选择某个人(John)，请执行以下操作:

SELECT player,count(distinct gameID) as numberofgames FROM game_table t1 JOIN ((SELECT DISTINCT player1 AS player FROM game_table) UNION (SELECT DISTINCT player2 AS player FROM game_table) UNION (SELECT DISTINCT player3 AS player  FROM game_table) ORDER BY player) t2 WHERE (t2.player=t1.player1 OR t2.player=t1.player2 OR t2.player=t1.player3) AND t2.player='John' GROUP by t2.player