如何统计多字段并计算占比？解决SQL语法错误及百分比计算问题

数据表结构 #

CREATE TABLE IF NOT EXISTS `wp_wpforms_entry_fields` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `entry_id` bigint(20) NOT NULL,
  `form_id` bigint(20) NOT NULL,
  `field_id` int(11) NOT NULL,
  `value` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `entry_id` (`entry_id`),
  KEY `form_id` (`form_id`),
  KEY `field_id` (`field_id`)
) ENGINE=InnoDB AUTO_INCREMENT=173 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

原查询的问题 #

原查询存在两个核心问题：

• 语法错误：FROM子句中的子查询未指定别名，MariaDB要求必须给子查询命名，否则会触发#1064语法错误。
• 统计范围错误：统计各类人群的子查询未限定form_id = 549，会统计所有表单的数据，导致结果与目标表单不匹配。

优化后的查询（含百分比计算） #

以下查询既修复了语法问题，又能准确统计目标表单的总报名数、各类型人数及对应占比：

SELECT
  total_enrolled AS `Total Enrolled`,
  supporters AS `Supporters`,
  ROUND((supporters / NULLIF(total_enrolled, 0)) * 100, 2) AS `Supporters %`,
  undecided AS `Undecided`,
  ROUND((undecided / NULLIF(total_enrolled, 0)) * 100, 2) AS `Undecided %`,
  opposition AS `Opposition`,
  ROUND((opposition / NULLIF(total_enrolled, 0)) * 100, 2) AS `Opposition %`
FROM (
  SELECT
    COUNT(DISTINCT entry_id) AS total_enrolled,
    SUM(CASE WHEN value = 'Supporter' THEN 1 ELSE 0 END) AS supporters,
    SUM(CASE WHEN value = 'Undecided' THEN 1 ELSE 0 END) AS undecided,
    SUM(CASE WHEN value = 'Opposition' THEN 1 ELSE 0 END) AS opposition
  FROM `wp_wpforms_entry_fields`
  WHERE form_id = 549
) AS stats;

关键说明： #

• 用COUNT(DISTINCT entry_id)直接统计目标表单的总报名人数，规避子查询别名问题。
• 通过SUM(CASE...)在一次表扫描中完成多维度统计，比多个独立子查询效率更高。
• NULLIF(total_enrolled, 0)避免总人数为0时出现除以0的运算错误。
• ROUND(..., 2)将百分比保留两位小数，可根据需求调整小数位数。

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