联赛脚本MySQL数据表及SQL查询开发技术问询

嘿，我把你这段关于联赛脚本数据表开发的内容整理成清晰的Markdown格式了，方便你后续调试和参考：

项目概况 #

我正在为一款联赛脚本开发对应的MySQL数据表，已经梳理好了完整的数据表结构，用于存储联赛赛事的核心数据（比如赛事记录、球队信息等）。

正在编写的积分榜统计SQL #

目前我正在写一个统计联赛积分榜的SQL查询，已经完成了部分逻辑，代码如下：

SELECT name AS Team, Sum(P) AS P, Sum(W) AS W, Sum(L) AS L, SUM(Pts) AS Pts 
FROM( 
    SELECT teamid1 AS Team, 1 AS P, 
           IF(team1score > team2score, 1, 0) AS W, 
           IF(team1score < team2score, 1, 0) AS L, 
           CASE 
               WHEN team1score > team2score THEN 1 
               WHEN team1score = team2score THEN 1 
               ELSE 0 
           END AS PTS 
    FROM games 
    UNION ALL 
    SELECT teamid2 AS Team, 1 AS P, 
           IF(team1score < team2score, 1, 0) AS W, 
           IF(team1score > team2score, 1, 0) AS L, -- 补充team2负场的判断逻辑
           CASE 
               WHEN team1score < team2score THEN 1 
               WHEN team1score = team2score THEN 1 
               ELSE 0 
           END AS PTS 
    FROM games
) AS team_stats
-- 需关联teams表获取球队名称（假设teams表有teamid和name字段）
JOIN teams ON team_stats.Team = teams.teamid
GROUP BY Team, name;

逻辑说明 #

• 用UNION ALL分别处理每场比赛的两支球队，统计各自的参赛次数、胜场、负场和积分
• 每一场比赛对两支球队来说都算1次参赛（P=1）
• 胜场（W）和负场（L）通过对比两队比分来判断
• 积分规则目前设置为胜或平得1分、负得0分，后续可根据联赛规则调整CASE语句里的分值

内容的提问来源于stack exchange，提问作者Maro