sql Select和group from db，其中team_1和team_2是相同的，但在条目中相反 - Msql Select and group from db where team_1 and team_2 are the same but reversed in the entry

Msql Select and group from db where team_1 and team_2 are the same but reversed in the entry

本文关键字：team from group Select db 其中 sql | 更新日期: 2023-09-27

我需要从数据库中选择team_1和team_2(足球比赛赛程)并按比赛分组

 $query = mysql_query("SELECT * FROM fixtures 
                       GROUP BY team_1 
                       ORDER BY match_date 
                       LIMIT 0,10") or die(mysql_error());

可以正常工作，但是，足球赛程将通过用户输入，因此，有人可以输入eg

英格兰vs威尔士有人可以进入威尔士vs英格兰

他们显然是同一场比赛，如果team_1(英格兰)vs team_2(威尔士)与team_1(威尔士)vs team_2(英格兰)相同，我怎么能将比赛分组

SELECT field1, field2, match_date, ......
       ,GREATEST(team_1,team_2) as team1
       ,LEAST(team_1,team_2) as team2
       ,field6, field7
FROM fixtures
GROUP BY team1
ORDER BY match_date
LIMIT 10 OFFSET 0;

你可能应该对输入进行一些完整性检查，以确保游戏尚未进入数据库:

$my_team = mysql_real_escape_string(...);
$other_team = mysql_real_escape_string(...);
SELECT team_1, team_2, match_date 
FROM fixtures
WHERE match_date = $match_date 
  AND ((team_1 = '$my_team' AND team_2 = '$other_team' ) OR
       (team_1 = '$other_team' AND team_2 = '$my_team' ));

在此之后，如果该查询不匹配，则可以插入记录。通常情况下，你会先插入"主场"比赛的球队。

无论如何，如果你想在9月19日搜索球队'foo'的比赛，你可以这样做:

SELECT team_1, team_2, match_date FROM fixtures
WHERE match_date = '2011-09-19' AND
(team_1 = 'foo' OR team_2 = 'foo');`

好运。