如何使用表连接使MySQL DISTINCT与多列一起工作？

How can I made the query below select only one record? Each game has two records (one record for each team). If there are nine (9) games there would be eighteen (18) records. I want to select only one record, not both records, for each game.

如何在下面查询只选择一条记录？每个游戏都有两个记录（每个团队一个记录）。如果有九（9）个游戏，则会有十八（18）个记录。我想为每个游戏只选择一个记录，而不是两个记录。

If I use DISTINCT with only one column (game_id_2) it works fine or returns only nine records. However, if I try to add more columns the DISTINCT directive no longer works.

如果我只使用DISTINCT只有一列（game_id_2）它可以正常工作或只返回9条记录。但是，如果我尝试添加更多列，则DISTINCT指令不再起作用。

SELECT DISTINCT
B.game_id_2,
B.GmeYear,
B.GmeMonth,
B.GmeDay,
B.GmeDate,
B.GmeTime,
B.GmeOrd,
B.Home,
B.DivPlay,
L.Instit,
FROM BsbGme B LEFT JOIN LeagueTeam L
ON B.team_id = L.team_id
WHERE B.NonD1=''
AND B.team_id IN ($participant_str)
AND B.GmeMonth = $GameMonth
AND B.GmeDay = $GameDay
ORDER BY B.game_id_2 ASC

According to w3schools dot come the DISTINCT directive is supposed to work with multiple columns. But it's not working with multiple columns in my example.

根据w3schools dot来说，DISTINCT指令应该适用于多列。但是我的例子中没有使用多列。

w3schools example:

w3schools的例子：

SQL SELECT DISTINCT Syntax SELECT DISTINCT column_name,column_name FROM table_name;

SQL SELECT DISTINCT语法SELECT DISTINCT column_name，column_name FROM table_name;

http://www.w3schools.com/sql/sql_distinct.asp

http://www.w3schools.com/sql/sql_distinct.asp

I have looked at several of the other answers to this same question on * but I can't get any of them to work.

我已经在*上查看了同一个问题的其他几个答案，但我无法让它们中的任何一个起作用。

Thank you in advance.

先谢谢你。

1 个解决方案

#1

try using brackets DISTINCT (your, columns) or use GROUP BY.

尝试使用括号DISTINCT（您的，列）或使用GROUP BY。