如何在SQL中一次性统计视图多列的选择次数并生成聚合报表

当然可以不用写多个单独的分组查询！我来给你一个简洁通用的方案，不用重复写分组语句就能得到你想要的报表格式～

核心思路：数据重构+条件聚合 #

我们先把分散在三列的选择数据统一整理成「选择位+选项值」的行格式，再通过条件聚合一次性统计每个选项在不同位置的选中次数，这种方法几乎适用于所有关系型数据库。

第一步：把多列数据转成统一行结构 #

用UNION ALL将原来的三个选择列拆分成两列：一列标记数据原本属于哪个选择位（1st/2nd/3rd），另一列存对应的选项值。这样所有选择数据就都在同一个维度里了：

SELECT '1stChoice' AS ChoiceType, 1stChoice AS Field FROM myView
UNION ALL
SELECT '2Choice' AS ChoiceType, 2Choice AS Field FROM myView
UNION ALL
SELECT '3rdChoice' AS ChoiceType, 3rdChoice AS Field FROM myView

这个子查询的输出示例：

第二步：条件聚合统计次数 #

基于上面的结果，用CASE语句判断每条记录对应的选择位，结合COUNT函数统计次数（COUNT会自动忽略NULL值，正好对应未命中的情况）：

SELECT
    Field,
    COUNT(CASE WHEN ChoiceType = '1stChoice' THEN 1 END) AS '1stChoice',
    COUNT(CASE WHEN ChoiceType = '2Choice' THEN 1 END) AS '2Choice',
    COUNT(CASE WHEN ChoiceType = '3rdChoice' THEN 1 END) AS '3rdChoice'
FROM (
    SELECT '1stChoice' AS ChoiceType, 1stChoice AS Field FROM myView
    UNION ALL
    SELECT '2Choice' AS ChoiceType, 2Choice AS Field FROM myView
    UNION ALL
    SELECT '3rdChoice' AS ChoiceType, 3rdChoice AS Field FROM myView
) AS PivotedData
GROUP BY Field
ORDER BY Field;

可选：用PIVOT语法简化（部分数据库支持） #

如果你的数据库支持PIVOT语法（比如SQL Server、Oracle、PostgreSQL 11+），可以用更简洁的写法实现相同效果：

SELECT
    Field,
    [1stChoice], [2Choice], [3rdChoice]
FROM (
    SELECT '1stChoice' AS ChoiceType, 1stChoice AS Field FROM myView
    UNION ALL
    SELECT '2Choice' AS ChoiceType, 2Choice AS Field FROM myView
    UNION ALL
    SELECT '3rdChoice' AS ChoiceType, 3rdChoice AS Field FROM myView
) AS SourceData
PIVOT (
    COUNT(Field)
    FOR ChoiceType IN ([1stChoice], [2Choice], [3rdChoice])
) AS PivotTable
ORDER BY Field;

最终输出结果 #

不管用哪种方法，运行后都会得到你期望的报表格式：

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