PostgreSQL查询如何展示schedules表无对应记录的所有作业

• 原语句使用INNER JOIN关联sampleco_schedules表，仅会保留两边都存在匹配关系的记录，因此没有对应日程记录的作业会被直接过滤
• 当作业无日程时，min(sc.start_time)、max(sc.end_time)返回NULL，to_char处理后结果为空白，没有替换为指定提示文本

修改后的完整查询语句如下：

select
    j.job_id as "Job No",
    c.business_name as "Customer",
    to_char(j.date_created, 'DD/MM/YYYY') as "Created Date",
    coalesce(to_char(min(sc.start_time), 'DD/MM/YYYY'), 'no schedule') as "Schedule Start",
    coalesce(to_char(max(sc.end_time), 'DD/MM/YYYY'), 'no schedule') as "Schedule End",
    to_char(j.date_due, 'DD/MM/YYYY') as "Due Date"
from sampleco_jobs j
join sampleco_customers c
on j.customer_id = c.customer_id
left join sampleco_schedules sc
on j.job_id = sc.job_id
where c.customer_id = 29726
group by j.job_id, c.business_name, j.date_created, j.date_due;

• 把原语句中关联sampleco_schedules的JOIN改为LEFT JOIN，即可保留所有目标客户下的作业记录，无论是否存在关联日程
• 使用COALESCE函数判断日期转换结果，当值为NULL时自动替换为no schedule
• 额外补充了j.date_created、j.date_due到GROUP BY子句中，符合PostgreSQL的严格分组模式要求，避免部分版本数据库执行报错

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