基于四张关联MySQL表按区域统计候选人得票数的SQL查询问题

现有四张关联MySQL表，表结构如下：

表 tblvotes #

表 tblcandidate #

表 tbldistricts #

表 tblvoterlist #

我尝试编写了如下SQL语句：

SELECT concat_ws(",", tvl.lastname, tvl.firstname) as candidate, td.district_name as district,  count(tv.candidateid) 
FROM tblvotes tv
JOIN tblcandidate tc on tv.candidateid = tc.id
JOIN tbldistricts td on tv.districtid = td.id
JOIN tblvoterlist tvl on tc.voterid = tvl.id

我也尝试按tv.districtid分组，但不确定COUNT函数的正确使用位置，也不理解如何利用表的关联键实现期望的结果。我期望得到的结果是交叉表形式，示例如下：

请求帮助编写正确的SQL语句实现该需求。

要实现这种交叉表（行转列）的统计，需要用到MySQL的条件聚合结合分组，具体SQL语句如下：

SELECT 
    td.district_name AS ``,
    COUNT(CASE WHEN concat_ws(',', tvl.lastname, tvl.firstname) = 'Candidate1' THEN tv.id END) AS `Candidate1`,
    COUNT(CASE WHEN concat_ws(',', tvl.lastname, tvl.firstname) = 'Candidate2' THEN tv.id END) AS `Candidate2`,
    COUNT(CASE WHEN concat_ws(',', tvl.lastname, tvl.firstname) = 'Candidate3' THEN tv.id END) AS `Candidate3`,
    COUNT(CASE WHEN concat_ws(',', tvl.lastname, tvl.firstname) = 'Candidate4' THEN tv.id END) AS `Candidate4`
FROM tbldistricts td
LEFT JOIN tblvotes tv ON td.id = tv.districtid
LEFT JOIN tblcandidate tc ON tv.candidateid = tc.id
LEFT JOIN tblvoterlist tvl ON tc.voterid = tvl.id
GROUP BY td.id, td.district_name
ORDER BY td.district_name;

关键说明： #

1. LEFT JOIN的使用：确保即使某个选区没有给特定候选人投票，也能显示0而不是过滤掉该行。
2. 条件聚合：通过CASE WHEN判断当前行属于哪个候选人，用COUNT统计有效记录（非NULL的情况），没有匹配的会返回NULL，COUNT会忽略NULL，从而得到0的结果。
3. 分组规则：按选区的ID和名称分组，确保每个选区只返回一行统计结果。
4. 候选人名称替换：需要把语句中的'Candidate1'等替换为实际的候选人姓名（格式为姓氏,名字，和concat_ws的结果一致）。

如果候选人是动态的（数量不固定），MySQL无法直接通过静态SQL实现，这种情况下需要用存储过程拼接动态SQL来生成对应列。

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