sql - 如何将来自不同表的这两个查询合并为一个来计算百分比？

Question

我有以下查询，其中包含该期间的学生出勤情况:

select total_presences from diary.period_attendance 
where id_customer = 1492 and id_diary_period = 172818 and id_user = 835603;

而且我有同期的课时数。

select count(*) from diary.lesson where id_diary_period = $1 and id_customer = $2 and end_date < now();

我想将 total_presences 除以课时数以获得学生的出勤率。

如何在单个查询中执行此操作？

Answer 1

可能最简单的方法是使用 CTE:

WITH lesson_count AS (
   select count(*) as lessons
   from diary.lesson 
   where id_diary_period = $1 and id_customer = $2 and end_date < now()
)
select total_presences, total_presences/lessons
from diary.period_attendance, lesson_count
where id_customer = 1492 
  and id_diary_period = 172818 
  and id_user = 835603;

根据 total_presences 的类型，您可能必须将其转换为数字、实数或 float 以避免整数运算。