如何匹配playerGuids与guidDamageHealing获取对应伤害及治疗数据

我有一张名为guild_points_encounter_history的表，其中guidDamageHealing字段存储的是逗号分隔的三组数据：第一个值为玩家guid，第二个是伤害值，第三个是治疗值。另外有一个存储逗号分隔guid的变量$playerGuids。需要实现将guidDamageHealing的第一个值与$playerGuids中的每个MEMBER.guid匹配，获取对应玩家的伤害和治疗数据。现有查询函数如下：

public function getHistoryDetails($historyId)
{
    $historyData = $this->getHistoryGlobalData($historyId);

    if ($historyData) {
        $bossEntry = $historyData['bossEntry'];
        $playerGuids = $historyData['playerGuids'];
        $leaderGuid = $historyData['leaderGuid'];

        if (strlen($playerGuids) > 0 && !empty($leaderGuid)) {
            $playerGuids = substr($playerGuids, 0, -1);

            $this->connect();

            $result = $this->connection->query("
                SELECT
                    MEMBER.guid,
                    MEMBER.name,
                    MEMBER.race,
                    MEMBER.class,
                    MEMBER.gender,
                    CASE WHEN MEMBER.race IN (1,3,4,7,11) THEN 'A'
                            WHEN MEMBER.race IN (2,5,6,8,10) THEN 'H'
                            ELSE 'X'
                    END AS faction,
                    CASE WHEN MEMBER.guid = " . $leaderGuid . " THEN 1 ELSE 0 END as isLeader,
                    GUILD.name as guildName,
                    GUILD.guildid,
                    STATS.achievementPoints,
                    STATS.GearScore,
                    STATS.RolPosible,
                    IFNULL(TEMPLATE_LOCALE.Name, TEMPLATE.name) as bossName,
                    HISTORY.ReadableTime,
                    HISTORY.mode as modo,
                    HISTORY.difficulty as dificultad,
                    HISTORY.guidDamageHealing
                FROM
                    characters MEMBER
                LEFT JOIN
                    guild_member GUILDMEMBER ON (GUILDMEMBER.guid = MEMBER.guid)
                LEFT JOIN
                    guild_points_encounter_history HISTORY ON (HISTORY.id = " . $historyId . ")
                LEFT JOIN
                    guild GUILD ON (GUILDMEMBER.guildid = GUILD.guildid)
                LEFT JOIN
                    character_stats STATS ON (STATS.guid = MEMBER.guid)
                INNER JOIN
                    world.creature_template TEMPLATE ON (TEMPLATE.entry = " . $bossEntry . ")
                LEFT JOIN
                    world.creature_template TEMPLATE_LOCALE ON (TEMPLATE.entry = TEMPLATE_LOCALE.entry)
                WHERE
                    MEMBER.guid IN (" . $playerGuids . ")
                ORDER BY
                    isLeader DESC
            ");

            if($result && $result->num_rows() > 0)
            {
                return $result->result_array();
            }

            unset($result);
        }
    }

    return false;
}

public function getHistoryDetails($historyId)
{
    $historyData = $this->getHistoryGlobalData($historyId);

    if ($historyData) {
        $bossEntry = $historyData['bossEntry'];
        $playerGuids = $historyData['playerGuids'];
        $leaderGuid = $historyData['leaderGuid'];

        if (strlen($playerGuids) > 0 && !empty($leaderGuid)) {
            // 安全移除末尾多余的逗号
            $playerGuids = rtrim($playerGuids, ',');

            $this->connect();

            $result = $this->connection->query("
                SELECT
                    MEMBER.guid,
                    MEMBER.name,
                    MEMBER.race,
                    MEMBER.class,
                    MEMBER.gender,
                    CASE WHEN MEMBER.race IN (1,3,4,7,11) THEN 'A'
                            WHEN MEMBER.race IN (2,5,6,8,10) THEN 'H'
                            ELSE 'X'
                    END AS faction,
                    CASE WHEN MEMBER.guid = " . $leaderGuid . " THEN 1 ELSE 0 END as isLeader,
                    GUILD.name as guildName,
                    GUILD.guildid,
                    STATS.achievementPoints,
                    STATS.GearScore,
                    STATS.RolPosible,
                    IFNULL(TEMPLATE_LOCALE.Name, TEMPLATE.name) as bossName,
                    HISTORY.ReadableTime,
                    HISTORY.mode as modo,
                    HISTORY.difficulty as dificultad,
                    -- 提取当前玩家对应的伤害值
                    SUBSTRING_INDEX(
                        SUBSTRING_INDEX(
                            SUBSTRING_INDEX(HISTORY.guidDamageHealing, CONCAT(MEMBER.guid, ','), -1),
                            ',', 1
                        ),
                        ',', -1
                    ) AS damage,
                    -- 提取当前玩家对应的治疗值
                    SUBSTRING_INDEX(
                        SUBSTRING_INDEX(
                            SUBSTRING_INDEX(HISTORY.guidDamageHealing, CONCAT(MEMBER.guid, ','), -1),
                            ',', 2
                        ),
                        ',', -1
                    ) AS healing
                FROM
                    characters MEMBER
                LEFT JOIN
                    guild_member GUILDMEMBER ON (GUILDMEMBER.guid = MEMBER.guid)
                LEFT JOIN
                    guild_points_encounter_history HISTORY ON (HISTORY.id = " . $historyId . ")
                LEFT JOIN
                    guild GUILD ON (GUILDMEMBER.guildid = GUILD.guildid)
                LEFT JOIN
                    character_stats STATS ON (STATS.guid = MEMBER.guid)
                INNER JOIN
                    world.creature_template TEMPLATE ON (TEMPLATE.entry = " . $bossEntry . ")
                LEFT JOIN
                    world.creature_template TEMPLATE_LOCALE ON (TEMPLATE.entry = TEMPLATE_LOCALE.entry)
                WHERE
                    MEMBER.guid IN (" . $playerGuids . ")
                    -- 过滤出有对应伤害治疗数据的玩家
                    AND HISTORY.guidDamageHealing LIKE CONCAT('%', MEMBER.guid, ',%')
                ORDER BY
                    isLeader DESC
            ");

            if($result && $result->num_rows() > 0)
            {
                return $result->result_array();
            }

            unset($result);
        }
    }

    return false;
}

• 替换substr($playerGuids, 0, -1)为rtrim($playerGuids, ',')，更安全地处理末尾逗号（避免原方法在字符串无末尾逗号时截断最后一个guid的问题）。
• 在SELECT语句中新增damage和healing字段，通过三层SUBSTRING_INDEX拆分guidDamageHealing字段，精准提取当前玩家对应的伤害、治疗数据。
• 添加WHERE条件AND HISTORY.guidDamageHealing LIKE CONCAT('%', MEMBER.guid, ',%')，确保只返回存在对应伤害治疗记录的玩家。

内容的提问来源于stack exchange，提问作者Vicente Juan Martí