求助：MariaDB中统计候选人对应职位得票数的SQL查询问题

现有votes表数据 #

需求 #

编写SQL查询统计得票结果，期望输出格式为：

john, for the referendum, has 4 votes
mark, for the referendum has 2 votes
sofia, for the premier has 3 votes
anna, for the premier has 1 votes

尝试的SQL与报错 #

尝试执行以下SQL语句：

SELECT DISTINCT 
    candidate, 
    position, 
    count(DISTINCT candidate) over (order by position) AS votes_received 
from votes; 

触发报错：

This version of MariaDB does not yet support 'COUNT (DISTINCT) aggregate as window function

无需使用窗口函数，直接通过分组聚合结合字符串拼接即可实现需求：

SELECT 
    CONCAT(candidate, ', for the ', position, ' has ', COUNT(*), ' votes') AS result
FROM votes
GROUP BY candidate, position
ORDER BY position, COUNT(*) DESC;

说明 #

• GROUP BY candidate, position：按候选人和对应职位分组，确保每个候选人在对应职位的得票被单独统计
• COUNT(*)：统计每组的记录数，即该候选人的得票数
• CONCAT：将候选人、职位、票数与固定文本拼接成目标格式的字符串
• ORDER BY：按职位和票数排序，让结果展示更有序（可根据需求调整排序规则）

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