如何合并SQL查询自动遍历州众议员选区计算提案得票总和？

我有一张按选区（Precinct）划分的选举结果表cmr.cmr2，需要生成每个州众议员选区（State Representative District）中特定提案（Proposition）的得票统计报告，需完成以下三步：

• 确定所有存在的州众议员选区；
• 找出每个州众议员选区各自包含的Precinct；
• 汇总每个州众议员选区下所有Precinct中特定提案的得票结果。

我已能单独实现各步骤的查询，但无法将它们合并为一个查询来自动遍历所有州众议员选区，请求帮助。

已实现的单个步骤查询 #

1. 列出所有州众议员选区

SELECT distinct Office_Title FROM cmr.cmr2
where Office_Title LIKE 'State Representative - District%'

2. 查询单个州众议员选区的Precinct

SELECT distinct CONCAT(County_Name, '-', Precinct_Name) FROM cmr.cmr2 
where
Office_Title LIKE 'State Representative - District 1'

3. 计算单个州众议员选区中特定提案的得票总和

SELECT Office_Title, 
SUM(Yes_votes) as Yes_Total, 
SUM(No_votes) as NO_Total 
FROM cmr.cmr2 AS CMRA

where
CMRA.Office_Title LIKE 'Proposition B'
and
CONCAT(County_Name, '-', Precinct_Name) in
(
SELECT distinct CONCAT(County_Name, '-', Precinct_Name) FROM cmr.cmr2 AS CMRB
where
CMRB.Office_Title LIKE 'State Representative - District 10'
)

合并后的查询语句 #

可以通过关联子查询的方式，将三个步骤合并为一个查询，自动遍历所有州众议员选区并统计指定提案的得票：

SELECT 
    districts.District AS District,
    'Proposition B' AS Ballot_Measure,
    SUM(proposals.Yes_votes) AS Yes_votes,
    SUM(proposals.No_votes) AS No_votes,
    proposals.Election
FROM (
    -- 获取所有州众议员选区及其对应的Precinct唯一标识
    SELECT 
        DISTINCT 
        c.Office_Title AS District,
        CONCAT(c.County_Name, '-', c.Precinct_Name) AS Precinct_ID,
        c.Election
    FROM cmr.cmr2 c
    WHERE c.Office_Title LIKE 'State Representative - District%'
) districts
-- 关联目标提案的得票数据
JOIN (
    SELECT 
        CONCAT(p.County_Name, '-', p.Precinct_Name) AS Precinct_ID,
        p.Yes_votes,
        p.No_votes,
        p.Election
    FROM cmr.cmr2 p
    WHERE p.Office_Title = 'Proposition B' -- 替换为需要统计的提案名称
) proposals ON districts.Precinct_ID = proposals.Precinct_ID
-- 按选区和选举分组汇总
GROUP BY districts.District, proposals.Election
ORDER BY districts.District;

查询逻辑说明 #

1. 子查询districts：一次性提取所有州众议员选区，以及每个选区对应的Precinct唯一标识（County_Name-Precinct_Name），同时保留选举信息；
2. 子查询proposals：提取目标提案的所有Precinct得票数据；
3. 通过Precinct_ID关联两个子查询，按选区和选举分组后汇总得票总数，最后按选区排序输出。

样本数据执行结果 #

针对提供的样本数据，执行上述查询后会得到如下结果：

+-----------------------------------+-----------------------+-----------+----------+---------------+
| District                          | Ballot_Measure        | Yes_votes | No_votes | Election      |
+-----------------------------------+-----------------------+-----------+----------+---------------+
| State Representative - District 1 | Proposition B         | 855       | 397      | November 2018 |
| State Representative - District 2 | Proposition B         | 921       | 655      | November 2018 |
| State Representative - District 3 | Proposition B         | 1382      | 889      | November 2018 |
| State Representative - District 4 | Proposition B         | 1776      | 1052     | November 2018 |
+-----------------------------------+-----------------------+-----------+----------+---------------+

（注：样本数据中各选区对应的Precinct得票相加：比如District 1对应SOUTHEAST 3和4，Yes_votes=362+493=855，No_votes=163+234=397）

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