Sql GROUP BY和GROUP_CONCAT用于计算比赛排名

我相信下面的方法会产生你想要的结果

• i.e.
  • 注意，参赛者6在比赛3中并不符合您的预期结果(但可能应该是上面的结果，即M(comp1); M(comp2); 3(comp3)根据之前的结果)
  • 排名(+1.0位)也是如此

使用:

WITH 
    cte_comp_competitor_matrix AS (
        SELECT DISTINCT results.competition_id,c2.competitor_id FROM results JOIN results AS c2 ON 1
    ),
    cte_stage2 AS (
        SELECT competitor_id,competition_id,
             CASE cccm.competitor_id IN(SELECT competitor_id FROM results WHERE competition_id = cccm.competition_id) 
                WHEN 1 THEN competition_id ELSE 'M' END
            AS matched
        FROM cte_comp_competitor_matrix AS cccm ORDER BY competitor_id
    ),
    cte_stage3 AS (
        SELECT *,
            coalesce(
                (
                    SELECT competitor_ranking 
                    FROM results 
                    WHERE cte_stage2.competitor_id = results.competitor_id 
                        AND cte_stage2.competition_id = results.competition_id
                ),
                '+1.0'
            ) AS competitor_ranking
        FROM cte_stage2
    )
SELECT 
    competitor_id,
    group_concat(matched,';') AS competitions,
    group_concat(competitor_ranking,';') AS rankings
FROM cte_stage3
GROUP BY competitor_id
;

• cte_comp_competition_matrix检索每个竞争对手/竞争对手组合

  • 

• cte_stage2 (for want of a better name) applies the M (missing) `

• cte_stage3(更好的名字？？)应用排名(从而有效地为错过的比赛添加原始+额外的行)

  • 

• CTE =公共表表达式，这是一个临时表，持续执行期间(可以看到，您可以有多个CTE).它们已被用来逐步达到预期结果.

  • 他们没有故意简化为更短的代码，希望使他们更容易理解，