如何在SQLite中查询满足分数、姓氏多条件的学生占比

核心计算逻辑：占比=符合全部筛选规则的学生数/全表总学生数*100，需适配SQLite的数值计算、字符串处理特性，同时满足两类筛选要求：考试得分超出指定0-100区间、姓氏匹配规则。

关键实现细节 #

• 数值除法适配：SQLite默认整数相除会取整，需将分子乘以100.0转为浮点运算，避免结果偏差
• 除零异常处理：用NULLIF包裹总学生数统计，避免空表查询时报错
• 多条件合并：采用条件聚合逻辑单次扫表完成统计，无需嵌套多次查询，降低性能损耗
• 姓氏匹配适配：根据姓名存储格式调整提取规则：
  • 空格分隔的英文姓名（如Kunal Shah）：姓氏为最后一段，可通过SUBSTR(student_name, INSTR(student_name, ' ') + 1)提取
  • 逗号分隔的英文姓名（如Shah, Kunal）：姓氏为逗号前的段，可通过TRIM(SUBSTR(student_name, 1, INSTR(student_name, ',') - 1))提取
  • 中文姓名：姓氏为开头1-2个字符，可通过SUBSTR(student_name, 1, 2)提取后匹配

示例查询代码 #

以下为统计得分超出0-100区间、且姓氏以S开头的学生占比的查询，可根据实际需求调整筛选条件：

SELECT
  ROUND(
    COUNT(
      CASE
        -- 按需修改阈值即可调整得分筛选范围
        WHEN (test_score < 0 OR test_score > 100)
        -- 按需修改提取、判断逻辑即可调整姓氏匹配规则
         AND TRIM(SUBSTR(student_name, INSTR(student_name, ' ') + 1)) LIKE 'S%'
        THEN 1
      END
    ) * 100.0 / NULLIF(COUNT(student_id), 0),
    -- 按需调整位数即可修改返回结果的小数精度
    2
  ) AS eligible_student_percentage
FROM gradebook;

灵活调整说明 #

• 如需更换得分筛选范围，直接修改test_score < 下限 OR test_score > 上限的判断逻辑即可，例如要统计低于60分或高于95分的学生，可替换为test_score < 60 OR test_score > 95
• 如需更换姓氏匹配规则，修改姓氏提取语句和后面的判断条件即可，例如要精确匹配姓氏为Shah的学生，可替换为= 'Shah'，要匹配中文姓氏“张”，可替换为SUBSTR(student_name, 1, 1) = '张'

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