mysql如何3行合一行_MySQL如何合并3行并计算结果？

它看起来好像是你正在开始的东西，并且你的数据库设计已经非常规范化，这可能会导致性能问题。为了减少这种情况，我建议你在进一步研究之前考虑进行重组。玩家1，玩家2和玩家3都是玩家，因此能够被标准化。您现有表的每一行都可以被视为game或类似，因此可以进行规范化。然后，您将面临将玩家与游戏联系起来的任务。这样您就不会多次存储相同的玩家详细信息，并且所有针对表的查询在长期运行中最终会更快，即使初始插入更复杂一些。

players (

id INT UNSIGNED PK AI,

name VARCHAR

.... other player data ....

)

// Assumed a name here

games (

id INT UNSIGNED PK AI

.... game data ....

)

// Note the 2-field primary key here. Prevents 1 player being in the same game twice.

playersInGames (

game_id INT UNSIGNED PK,

player_id INT UNSIGNED PK,

playerNumber TINYINT UNSIGNED // You don't even need this, if you don't care about player number assigments just don't include this column

)示例数据

// players

id name

1 toto

2 tata

3 titi

4 tutu

// games

id

1

2

3

4

// playersInGames

game_id player_id playerNumber

1 1 1

2 2 1

2 1 2

3 3 1

3 4 2

4 2 1

4 3 2

4 1 3这反过来会让你做简单的查询，如

SELECT

p.*,

COUNT(DISTINCT pig.game_id) AS gamesPlayerIsIn

FROM players AS p

LEFT JOIN playersInGames AS pig

ON pig.player_id = p.id

GROUP BY p.id编辑，如果你真的不想更改表模式，以下将足够，但将使谁维护你的代码哭。它将每个玩家最多计算一次。

SELECT

players.playerName,

COUNT(DISTINCT players.id) AS numberOfRows

FROM (

(

SELECT DISTINCT id, player1 AS playerName FROM tableName

) UNION DISTINCT (

SELECT DISTINCT id, player2 AS playerName FROM tableName

) UNION DISTINCT (

SELECT DISTINCT id, player3 AS playerName FROM tableName

)

) AS players