如何编写SQL查询实现按输入s_id统计各问题yes/no回答数

涉及表结构说明 #

• question表（问题基础信息表）
  • 核心字段：
    • q_id：问题唯一标识
    • s_id：业务关联标识
    • question：问题具体文本
  • 示例数据：s_id=1下关联3个问题，q_id分别为8、9、10，对应问题文本为like coffee??、like water??、like tea??
• result表（答题记录表）
  • 核心字段：
    • id：答题记录唯一标识
    • q_id：关联的问题ID
    • s_id：业务关联标识
    • answer：用户回答结果，取值仅为yes或no
  • 预期统计效果（基于s_id=1的6条示例答题数据）：
    • like coffee??：yes回答1次，no回答1次
    • like water??：yes回答1次，no回答1次
    • like tea??：yes回答2次，no回答0次

具体需求 #

支持传入用户指定的s_id作为查询参数，返回固定三列的统计结果：

• question：问题文本内容
• yes：当前传入s_id下，对应问题回答为yes的总次数
• no：当前传入s_id下，对应问题回答为no的总次数

可直接使用的SQL实现 #

SELECT
    q.question,
    SUM(CASE WHEN r.answer = 'yes' THEN 1 ELSE 0 END) AS `yes`,
    SUM(CASE WHEN r.answer = 'no' THEN 1 ELSE 0 END) AS `no`
FROM question q
LEFT JOIN result r
    ON q.q_id = r.q_id
    AND q.s_id = r.s_id
WHERE q.s_id = ? -- 问号位置替换为用户传入的s_id参数即可
GROUP BY q.q_id, q.question;

实现注意点 #

• 采用LEFT JOIN以问题表为基准关联答题记录，避免某类回答数为0、或是问题无任何答题记录时，对应问题从结果集中丢失
• 关联时同时匹配q_id和s_id两个字段，防止跨业务标识的答题数据被错误统计
• 用条件聚合的方式分别统计yes、no的数量，相比多次子查询统计的写法执行效率更高
• 分组时同时带上唯一主键q_id和问题文本字段，兼容绝大多数数据库的SQL_MODE分组校验规则，不会触发语法报错

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