如何从多场地多类别投票表统计各场地各类别得票总数

一、场地为行、类别为列的交叉统计表 #

静态场景（category_id固定已知） #

如果投票类别固定不会新增，用CASE WHEN配合聚合函数就能实现行转列，以MySQL为例：

SELECT 
    venue_id,
    COUNT(CASE WHEN category_id = 1 THEN 1 END) AS category_1_votes,
    COUNT(CASE WHEN category_id = 2 THEN 1 END) AS category_2_votes,
    COUNT(CASE WHEN category_id = 3 THEN 1 END) AS category_3_votes
    -- 根据实际存在的category_id，继续添加对应的CASE语句
FROM votes
GROUP BY venue_id;

原理是对每个场地，通过CASE WHEN筛选出对应类别的投票，再用COUNT统计数量。

动态场景（category_id会动态新增） #

如果类别会随时新增，手动写CASE WHEN太繁琐，用动态SQL可以自动生成列：

MySQL版本

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(CASE WHEN category_id = ', category_id, ' THEN 1 END) AS category_', category_id, '_votes'
    )
  ) INTO @sql
FROM votes;

SET @sql = CONCAT('SELECT venue_id, ', @sql, ' FROM votes GROUP BY venue_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server版本（用PIVOT语法）

DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

-- 生成所有category_id的列名
SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(category_id) 
            FROM votes
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

-- 拼接透视查询语句
SET @query = 'SELECT venue_id, ' + @cols + ' from 
            (
                SELECT venue_id, category_id
                FROM votes
            ) x
            PIVOT 
            (
                COUNT(category_id)
                FOR category_id IN (' + @cols + ')
            ) p '

EXECUTE(@query)

二、单个类别下各场地的得票统计 #

直接过滤目标类别，按场地分组统计投票数即可，还能快速查看该类别的场地得票排名：

SELECT 
    venue_id,
    COUNT(vote_id) AS total_votes
FROM votes
WHERE category_id = 1 -- 替换为你要查询的具体category_id
GROUP BY venue_id
ORDER BY total_votes DESC; -- 可选：按得票数从高到低排序

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