SQL两个计数子查询相减设置total别名的实现与语法排查

你之前独立写的减法语句报错，主要是两个问题：

• 语句缺少关联上下文：独立查询里没有关联exams表，却直接引用了exams.id字段，数据库无法识别该字段来源，自然报语法错
• 违反SQL通用语法规则：同一个SELECT查询层级中，数据库不允许直接引用当前层级刚定义的列别名（比如同层级定义的studentstaken、studentsnotreviewed）做后续计算

推荐用零重复计算的实现方式：你现有查询已经通过内层派生表x提前算出了studentstaken和studentsnotreviewed两个统计值，直接在外层查询里引用这两个字段做减法即可，不需要重复执行两次count子查询，性能更优。

可直接运行的完整修正SQL：

select 
  x.*,
  -- 新增差值计算字段，直接复用派生表已算出的统计值
  (x.studentstaken - x.studentsnotreviewed) as total
from
(
select 
  schools.name as School,  
  exams.name as name, 
  exams.exam_start as examstart, 
  exams.exam_end as examend, 
  (select count(*) from students where exams.id=students.exam_id) as studentstaken,
  (select count(*) from students where exams.id=students.exam_id and (students.review_flag='' or students.review_flag is null)) as studentsnotreviewed,
  case when exam_end < (now() - interval 2 day) then 'Yes' else 'No' end as 'Overdue',
  exam_end + Interval 2 day as 'DueDate',
  (select value from exam_options where exams.id=exam_options.exam_id and option_id=5) as IDtoggle,
  (select value from exam_options where exams.id=exam_options.exam_id and option_id=6) as Roomscantoggle
from exams
left join taggables on exams.school_id=taggable_id
left join schools on exams.school_id=schools.id
where tag_id=12 
  and exam_start < now() 
  and exam_start>'2021-01-01' 
  and practice=0
) as x
where 
  studentsnotreviewed>0 
  and (studentsnotreviewed>15 or examend < now())  
  and (IDtoggle=1 or Roomscantoggle=1)
order by 
  duedate asc, 
  studentsnotreviewed desc

对照你提供的两条样例数据，计算结果完全匹配预期：

• 德克萨斯大学休斯顿健康科学中心2021-02-03的EXAM 1：26名参考学生 - 2名未审核学生 = total值24
• 同校2021-02-04的EXAM 1：31名参考学生 - 1名未审核学生 = total值30

• 如果你坚持要把total字段写在内层派生表的SELECT列表里，不能直接引用同层级的studentstaken、studentsnotreviewed别名，必须把两个count子查询的原始表达式搬过来相减，这种写法会重复执行两次count统计，性能比外层计算的方案差
• 后续写SQL遇到“无法识别列别名”的报错，基本都是违反了「同层级不能引用同层级别名」的规则，把计算逻辑挪到外层查询，或者重复写原始表达式就能解决

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