如何编写SQL查询计算《老大哥》提名的参与者得分？

我有一个名为voting的表，结构如下：

CREATE TABLE `voting` (
  `participant` varchar(11) NOT NULL,
  `firstVote` varchar(11) NOT NULL,
  `secondVote` varchar(11) NOT NULL
)

字段说明：

• participant：投票参与者
• firstVote：第一投票对象，计2分
• secondVote：第二投票对象，计1分

需要编写SQL查询，返回所有参与者及其获得的总得分——即使参与者未获得任何投票，得分也要显示为0。

示例数据：

期望查询结果：

通过提取所有参与者列表、拆分两种投票的得分项，再关联统计总分，具体SQL如下：

SELECT 
    p.participant,
    COALESCE(SUM(v.score), 0) AS votesReceived
FROM (
    -- 获取所有参与者的完整列表
    SELECT participant FROM voting
) p
LEFT JOIN (
    -- 统计第一投票的2分记录
    SELECT firstVote AS recipient, 2 AS score FROM voting
    UNION ALL
    -- 统计第二投票的1分记录
    SELECT secondVote AS recipient, 1 AS score FROM voting
) v ON p.participant = v.recipient
GROUP BY p.participant
ORDER BY votesReceived DESC;

1. 子查询p：从原表中提取所有参与者，确保无投票记录的参与者也能被纳入结果。
2. 子查询v：用UNION ALL合并两类投票的得分记录，把每一次投票拆分为"得分对象+对应分值"的条目。
3. LEFT JOIN：将参与者列表与得分记录关联，保证所有参与者都能被保留，未匹配到得分的记录会返回NULL。
4. COALESCE：将NULL转换为0，实现无投票参与者得分显示为0的需求。
5. GROUP BY：按参与者分组计算总分，最后按得分降序排序。

代入示例数据验证，结果与期望完全一致。

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