编写满足重要目标达成准入条件的玩家排行榜SQL查询语句

基础信息 #

表结构 #

现有3张业务数据表，结构如下：

PLAYER        (NAME)                     -- 玩家表，存储玩家姓名
ALL_GOALS     (TYPE, `GROUP`, POINTS)    -- 目标配置表，TYPE为目标唯一标识，GROUP为目标分组，POINTS为目标满分
GOALS_REACHED (TYPE, EARNED_POINTS, PLAYER_NAME) -- 玩家达成目标表，EARNED_POINTS为玩家实际获得积分

准入规则 #

玩家需至少达成1个GROUP='IMPORTANT'分组的目标，才可进入排行榜。

示例数据 #

-- PLAYER表数据
+--------------+
| NAME         |
+--------------+
| John         |
| Bill         |
| Bob          |
| Willy        |
+--------------+

-- ALL_GOALS表数据
+---------------------------+
| TYPE | `GROUP`   | POINTS |
+---------------------------+
| A    | IMPORTANT | 100    |
| B    | IMPORTANT | 200    |
| C    | OTHER     | 10     |
| D    | OTHER     | 10     |
+---------------------------+

-- GOALS_REACHED表数据
+------------------------------------+
| TYPE | EARNED_POINTS | PLAYER_NAME |
+------------------------------------+
| A    | 100           | John        |
| B    | 200           | Willy       |
| C    | 10            | Bob         |
| D    | 10            | Bob         |
+------------------------------------+

实现查询语句 #

使用EXISTS子查询过滤符合准入条件的玩家，汇总玩家所有已达成目标的总积分按降序排列生成排行榜：

SELECT 
    gr.PLAYER_NAME AS 玩家名称,
    SUM(gr.EARNED_POINTS) AS 总积分
FROM GOALS_REACHED gr
WHERE EXISTS (
    SELECT 1
    FROM GOALS_REACHED gr_imp
    INNER JOIN ALL_GOALS ag_imp 
        ON gr_imp.TYPE = ag_imp.TYPE
    WHERE 
        ag_imp.`GROUP` = 'IMPORTANT'
        AND gr_imp.PLAYER_NAME = gr.PLAYER_NAME
)
GROUP BY gr.PLAYER_NAME
ORDER BY 总积分 DESC;

注意：GROUP是SQL保留关键字，作为字段名使用时需要用反引号转义。

查询结果说明 #

示例数据下查询仅返回符合准入条件的2名玩家，排序后结果如下：

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